Consulting

Results 1 to 6 of 6

Thread: Add text in cell based on choice from drop down list

  1. #1
    VBAX Newbie
    Joined
    May 2011
    Posts
    3
    Location

    Add text in cell based on choice from drop down list

    I have created a drop down list in excel. From the list the user select the proper value. Based on the users selection I need VBA to fill in a predefined text in the cell to the right.
    The drop down list is in range B2:B10 and could contain values like "ABCD","AB12" etc and the text to be added in Cell C2 should be e.g "remember to fill in additional information by replacing this text"," no further documentation is required". All values from the drop down will have related text that should go to t C column. I have tested lookup/vlookup but cannot use it since the user should be able to replace the predefined text with his/her own words which is not possible if i have a formula in the cell. I hope this make sence and thanks for all help you can provide, best regards Oscar

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why not just create a table of dropdown values and associated text, and use a VLOOKUP formula to populate the adjacent cell.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    May 2011
    Posts
    3
    Location
    Since the user must write text into the cell where the vlookup formula is located it's not a option. And since I already have a VBA I would like to maintain all values from the editor. So any ideas?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Uh? That doesn't make sense. The user writes data to a cell, then you want to overwrite it with your own values?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Newbie
    Joined
    May 2011
    Posts
    3
    Location
    No, the user selects a value from a drop down list. Based on the users choice a predefined text is copied into same row/next column. The predefined text gives instructions to the user. The text instruction request the user to replace the text with users own text (as an explanation to why They chosed the value)
    Exampel on values from predefined drop down is
    From dropdown the user select "Event1" - and the corresponding text should always be "Replace this text with your explanations"
    From dropdown the user select "Event2" - and the corresponding text should always be "Replace this text with your ID"
    From dropdown the user select "Event3" - and the corresponding text should always be "Replace this text with your voluntary explanations" etc. I have totally 12 events.

  6. #6
    1. is the dropdown list data validation or an ActiveX drop down?
    2. If it's activeX drop down, then assign macro to the drop down list
    3. If it's data validation then write macro to under Worksheet_Change event

    Create VLOOKUP hidden elsewhere, lookup value equal to the drop down list value. Use VBA to change the desired value of the cell in to the value of the VLOOKUP cell.

    Sample Code:
    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = "$A$1") Then 'Assuming $A$1 is where your drop down cell value is, could be anywhere, if it's not in sheet, then you need to change the code for that worksheet.
    Range("Secret_VLOOKUP_Cell").Value = Range("Target_Cell").Value
    End If
    End Sub
    [/VBA]
    Last edited by leigao84; 05-24-2011 at 01:12 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •