Excel 2007 Linking Multiple Text

Thank you all for helping me!

Instead of J or Row 10 And K or Row 11 lets make it easier and just say A, B, C and D Columns.

D being the result(s) of A, B, and C

I have this code:


If Not Intersect(Target, Range("J5:J600")) Is Nothing Then
         'Selected with Other option
        If Cells(Target.Row, 10) = "N" Then
             'require data entry in Justification Cell
            Application.EnableEvents = False
            Do While Cells(Target.Row, 11) = ""
                 'Get data from user
                If SaveVal1 <> "" Then
                    Cells(Target.Row, 11) = InputBox("Enter data for OCA Staff Comments", "Mandatory data entry", SaveVal1)
                Else
                    Cells(Target.Row, 11) = InputBox("Enter data for OCA Staff Comments", "Mandatory data entry")
                End If
                 SaveVal = Cells(Target.Row, 11)
            Loop
            Application.EnableEvents = True
        End If
    End If


You can see that if "N" then Mandatory text in another column.  But what I really want is more complex.  This code can be used as a starting point?

Example:

User selects from a drop-down in column A (any row).  Based on the selection: Mandatory in Column D.
User selects from a drop-down in column B (any row).  Based on the selection: Mandatory in Column D.
User selects from a drop-down in column C (any row).  Based on the selection: Mandatory in Column D.

So Column D Would look like this:

Header from A: This is what the user typed into the messagebox.  Just some text that basically Justifies the users reason for selecting the option in A

Header from B: This is what the user typed into the messagebox.  Just some text that basically Justifies the users reason for selecting the option in B

Header from C: This is what the user typed into the messagebox.  Just some text that basically Justifies the users reason for selecting the option in C


The MessageBox would have to hold what is already in Column D.  Then rewrite it back to Column D along with the new text coming from the MessageBox that the user typed in?

There can not be any overwritting of the existing text in Column D

Here is another code example but needs to go to Column D instead of another worksheet:
Also don't pay attention to the fact that this is in Column D because this is just an example:

If Not Intersect(Target, Range("D1:D600")) Is Nothing Then
        For Each change In Target.Cells
            If change.Text <> "N/A" And Target.Text <> "" Then
                Application.EnableEvents = False
                'Get data from user
                Do While message = ""
                 message = InputBox("Enter Justification for Column " & Mid(change.Address, 2, 1), "Mandatory data entry")
                Loop
                '-- insert data in target sheet
                Worksheets("JUSTIFICATION").Range(change.Address) = message
                '-- insert comment in current sheet
                Cells(change.Row, 4).Hyperlinks.Add Cells(change.Row, 4), "", "JUSTIFICATION!" & change.Address, , change.Text
                Application.EnableEvents = True
            End If
        Next change
    End If



Thank you very much for helping me
Amour22015Asked:
Who is Participating?
 
gowflowCommented:
ok here it is
1) the worksheet is protected so cannot change anything manually in col D
2) any value if starting from blank is inputed in A B or C then Inputbox comes out blank to accept input
3) If data already in A B or C then the coresponding cell that is either beeing clicked on the arrow to change the item or doubleclick on the cell give you in the inputbox what was already there you may change anyhing in the inputbox and when ok is pressed then it is updated again replacing the original message.

Any qestion pls ask
By the way ... its a weired project you have and remember previous comments from several Experts telling you about it ... !!!

Anyway we are here to help you thru it
gowflow
ABCtoD.xls
0
 
gowflowCommented:
ok but you did not state what you want to acheive !!! I read the whole issue and understand but what do you want from the second code ?
gowflow
0
 
hnasrCommented:
Post the sheet, with steps to do and what to expect.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gowflowCommented:
Hi Amour22015
Is this what you expect from the second Sub to do ??

I called it NewTest and you can callit from the Worksheetchange event this way
NewTest Target

type anything in col D from row 6 downward and see if this is what your looking for !
We can automate it and change the reference to column D if you need this to look for other columns first check if it is doing what you want then will see the rest later.

gowflow
Sub NewTest(Target As Range)
Dim Change As Range
Dim Message As String

If Not Intersect(Target, Range("D1:D600")) Is Nothing Then
        For Each Change In Target.Cells
            If Change.Text <> "N/A" And Target.Text <> "" Then
                Application.EnableEvents = False
                'Get data from user
                Do While Message = ""
                 Message = InputBox("Enter Justification for Column " & Mid(Change.Address, 2, 1), "Mandatory data entry")
                Loop
                '-- insert data in target sheet
                Worksheets("JUSTIFICATION").Range(Change.Address) = Message
                '-- insert comment in current sheet
                Cells(Change.Row, 4) = Cells(Change.Row, 4) & " " & Message
                Cells(Change.Row, 4).Hyperlinks.Add Cells(Change.Row, 4), "", "JUSTIFICATION!" & Change.Address, , Change.Text
                Application.EnableEvents = True
            End If
        Next Change
    End If

End Sub

Open in new window

0
 
gowflowCommented:
CORRECTION !!!

Reading your post again for the 7th time I think I have a slight clue of what your trying to acheive.

If the following explanation is correct the the below code will do it.

User type in anything in Col A or B or C from line 1 to 600 and everytime they type in anything it shows in Col D of the same row by adding to what is already in that cell already
EXAMPLE:

Col A, B C D at row 10 are all blank
user type in Dunky in A10 then the inputbox comes up and he type in Mystery
Result in D10 Dunky Mystery (as hyperlink blue underline)

user type in Joe in B10 then the inputbox comes up and he type in Blow
Result in D10 Dunky Mystery Joe Blow (as hyperlink blue underline)

user type in what a shame! in C10 then the inputbox comes up and he type in Totally mis-placed !!
Result in D10 Dunky Mystery Joe Blow what a shame! Totally mis-placed !! (as hyperlink blue underline)

if above is what you want the this macro will do it.
Just call it from worksheet_change event like this
ABCtoD Target

so it will lokk like this:
=========================
Private Sub Worksheet_Change(ByVal Target As Range)
ABCtoD Target
End Sub
=========================

gowflow
Sub ABCtoD(Target As Range)
Dim Change As Range
Dim Message As String

If Not Intersect(Target, Range("A1:C600")) Is Nothing Then
        For Each Change In Target.Cells
            If Change.Text <> "N/A" And Target.Text <> "" Then
                Application.EnableEvents = False
                'Get data from user
                Do While Message = ""
                 Message = InputBox("Enter Justification for Column " & Mid(Change.Address, 2, 1), "Mandatory data entry")
                Loop
                '-- insert data in target sheet
                Worksheets("JUSTIFICATION").Range(Change.Address) = Message
                '-- insert comment in current sheet
                Cells(Change.Row, 4) = Cells(Change.Row, 4).Text & " " & Change.Text & " " & Message
                Cells(Change.Row, 4).Hyperlinks.Add Cells(Change.Row, 4), "", "JUSTIFICATION!" & Change.Address, , Cells(Change.Row, 4).Text
                Application.EnableEvents = True
            End If
        Next Change
    End If

End Sub

Open in new window

0
 
Amour22015Author Commented:
gowflow


Thank you have helping me!

You are making it a little more complex then what I need.

I have drop-down's in Columns A,B,C

The header in Column A:

Animals

The Drop-Down in Column A:
Cat
Dog
Pig
Cow

User Selects:
Cat

Mandatory MessageBox comes up
User types in the reason why they selected Cat:

The cat in the hat came back to the house. oasndfoedfn  alsdfldsfm  .asd.f
what ever they type in is ok........

then in Column D:

Animals: The cat in the hat came back to the house. oasndfoedfn  alsdfldsfm  .asd.f
what ever they type in is ok........

Column B header is:
Books

Column B drop-down:
Novel
Christmas
Fantasy
Real
Ficton

User selects:
Christmas

Mandatory MessageBox
User types in the reason why they selected Christmas:

Was the night before Christmas. oasndfoedfn  alsdfldsfm  .asd.f
what ever they type in is ok........

then in Column D now looks like:

Animals: The cat in the hat came back to the house. oasndfoedfn  alsdfldsfm  .asd.f
what ever they type in is ok........

Books: Was the night before Christmas. oasndfoedfn  alsdfldsfm  .asd.f
what ever they type in is ok........

Column C:
Same logic

And each time it also writes back to the Columns A,B,C a link to Column D...

Thank you very much!
0
 
Amour22015Author Commented:
gowflow

thank you,

I don't want another worksheet("JUSTIFICATION"), that was just a sample to help find the solution.  this is all going to happen in the same worksheet.

Thank you!
0
 
gowflowCommented:
ok fine just now read your post will do but question you need a blank row every time in between the items selected right ? so users can clik 100 times A B or C or any then you will have 100 paragraphs each for A or B or C with what they typed in and each para separated by a blanck row all in Col D right ?
gowflow
0
 
gowflowCommented:
Is this what you want ?
gowflow
ABCtoD.xls
0
 
Amour22015Author Commented:
gowflow

Thank you,

Your example:

Column A, B, C
Animals
Books
Houses

are not in the proper cell:
 Cells(target.rows, 4)
in this case it should only be D2

they are in
D1,D3,D5

What I want?
User selects from drop-down A2, B2, and C2 then
D2 should look like:
Animals: This is a test of Cell A2, This should be in D2 with a blank line that follows

Books: This is a test of Cell B2, This should be in D2 with a blank line that follows

Houses: This is a test of Cell C2, This should be in D2 with a blank line that follows

This is done per ROW, so if I have 600 Rows then
User selects from drop-down A4, B4, and C4 then
D4 should look like:
Animals: This is a test of Cell A4, This should be in D4 with a blank line that follows

Books: This is a test of Cell B4, This should be in D4 with a blank line that follows

Houses: This is a test of Cell C4, This should be in D4 with a blank line that follows

Another example:
User selects from drop-down A600, B600, and C600 then
D600 should look like:
Animals: This is a test of Cell A600, This should be in D600 with a blank line that follows

Books: This is a test of Cell B600, This should be in D600 with a blank line that follows

Houses: This is a test of Cell C600, This should be in D600 with a blank line that follows


I hope this is more clear?

Your question:
but question you need a blank row every time in between the items selected right ?

No, I need a blank line

so users can clik 100 times A B or C or any then you will have 100 paragraphs each for A or B or C with what they typed in and each para separated by a blanck row all in Col D right

No, I only want per Row.  What they type in goes to the same ROW.  Plus I don't understand your question?

User selects drop-down in A10, B10, C10 mandatory entry in D10
User selects drop-down in A1-A600, B1-B600, C1-C600 mandatory entry in D1-D600


Thank you for helping me!
0
 
gowflowCommented:
Is this what you want ?
gowflow
ABCtoD.xls
0
 
Amour22015Author Commented:
gowflow

Thank you for helping me!

This is on the correct track, but just one more thing?

Example:
When Column A,B or C has mandatory entry.  The only way to edit the text(not the Header Title) in Column D is to Click in Columns A, B or C?
Lets say Column A writes to Column D:
Animals: This is a test From ColumnA I picked Cow

I want a link back to Column A linking with the selection (in this case) Cow? To:
Animals: This is a test From ColumnA I picked Cow

Lets say Column B writes to Column D:
Books: This is a test from Column B I picked Real

I want a link back to Column B linking with the selection (in this case) Real? To:
Books: This is a test from Column B I picked Real

Lets say Column C writes to Column D:
Houses: This is a test from Column C I picked Villa

I want a link back to Column C linking with the selection - Villa? To:
Houses: This is a test from Column C I picked Villa

I don't want:
Clicking on Column D to open any text that has been placed, this is protected.
Or:
Animals: This is a test From ColumnA I picked Cow

Books: This is a test from Column B I picked Real

Houses: This is a test from Column C I picked Villa

Animals: This line should not be here I only want the 3 above

Books: This line should not be here I only want the 3

Houses: This line should not be here I only want the 3

Example:
If user selects the LINK in column A then:
This is a edit of:
Animals: This is a test From ColumnA I picked Cow
If user selects the LINK in column B then:
This is a edit of:
Books: This is a test from Column B I picked Real
If user selects the LINK in ColumnC then:
Houses: This is a test from Column C I picked Villa

Thank you for helping me!
0
 
gowflowCommented:
Please can you explain in plain english in a sentence or 2 max what should be modified ?? I am lost with your examples
gowflow
0
 
gowflowCommented:
In 2 words I think what I did so far is what you want except
When data has been entered in A B or C and is populated in D you want:
1) D not to be edited at all
2) D can only be edited via selecting the Cell A or B or C and when this happens it will populate in the inputbox the text already saved for extra editing and the user can then save a new version for A or B or C

Is that what you want ?
gowflow
0
 
Amour22015Author Commented:
gowflow

Thank you,

When data has been entered in A B or C and is populated in D you want

When Data has been selected(from drop-down) in Columns A,B or C and is populated in D I want:
2) D can only be edited via selecting the Cell A or B or C and when this happens it will populate in the inputbox the text already saved for extra editing and the user can then save a new version for A or B or C over the existing ones.

2) D can only be edited via selecting the links in Columns A or B or C and when this happens it will:

If user selects columnA
Only allow edit on Animals:
populate in the inputbox the text already saved for A and the user can then edit the existing version for A and replace it back to the same location
If user Selects ColumnB
Only allow edit on Books:
populate in the inputbox the text already saved for B and the user can then edit the existing version for B and replace it back to the same location
If user Selects ColumnC
Only allow edit on Houses:
populate in the inputbox the text already saved for C and the user can then edit the existing version for C and replace it back to the same location



I hope this is less confussing?

Thank you very much!

0
 
Amour22015Author Commented:
gowflow
Thank you that was it!

Just what I am looking for.

Thanks again in MERRY CHRISTMAS!!!
0
 
gowflowCommented:
Merry Christmas to you too and tks glad we finally got what you wanted.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.