Drop-down menu that adds cell value in a: text/text/text/text format?

[MattDW]

Is there any method in which using a combination of VBA and data validation in Excel I'd be able to select values from a drop-down menu and have it add this text back to the cell value in a: text/text/text/text format?

I have a cell (current row of column R) in which I keep track of the types of problems the machine in that row represents. I add as many as are applicable so that cell may contain text that looks like this: Hard Drive/LCD/System Board/Screws.

I later need to compare this string from the cell in column R to the display text of a bunch of checkboxes on a website. That comparison will then become the catalyst for checking as many of the checkboxes that match.

The checkboxes on the website form have the following text:

FanFailure
MotherboardFailure
NICFailure
BrokenPlastics
WillnotPowerOn
CatastrophicDamage/BeyondEconomicRepair
LCDScreenBroken
LCDBacklightDim
DC-in/PowerPortDamage
KeyboardDamage
TrackpadErratic/Damage
BatteryFailure
LooseorMissingMotherboardScrews
WiFiFailure
SSD/HDDFailure
z_Other-PleasebeExplicitinnotes

My idea is to take the string from column R, split the text using the split function, using a delimiter of /, and to then use a series of If statements to compare. If there is a match then I can call that checkbox by ID to place a checkmark in it.

Taking the string value from column R, splitting the values and adding it to an array isn't a problem (and I have the code written that does just that). The main issue I'm running into is that by allowing a user free reign to enter any text they like, there are too many possible variations, which makes later matching of the values later on potentially difficult, and so I'd love to have some "rails" that prompts the user what to enter by having them choose those entries from a drop-down menu.

My first thought was using data validation but data validation isn't flexible enough in that if you point data validation to a list, and you choose an entry from the resulting drop-down menu, that single selection is the only entry that is entered into that cell. What I'd like is more of a choose all that applies rather than choose one single entity - and the later is currently how data validation drop-down menus, on their own, work.

Is there any method (using a combination of data validation and VBA) in which entries could be chosen from a drop-down menu but those entries are added to a string, formatted in the text/text/text/text format using VBA and then to have the final string added back as the cell's value?

This way the user will have some guideline as to what the entry should be by selecting those values from the drop-down menu (which takes away the variation) but I still leaves a value in a format that is easy to grab, break apart, and compare to the textbox display text.

Thanks much for any ideas you might have!

 

[MattDW]

So I'm been playing around with an idea that is mostly working, but I'm not sure if this is really the best way to approach the problem? Is there a better way to accomplish this or is this the approach others here would take as well?

This is mostly doing what I want. But the logic gets sort of funky at times, including if I try and manually edit the value (say I want to manually delete one of the values in the cell). I'm not quite sure how I should handle that situation using code.

With this code, when I select a cell for the first time it uses a declared global variable, CurrentCellValue, to capture the current value for that cell. When I choose an item from the drop-down menu it captures that as the new value, ChangedCellValue, and it adds that back to a string called NewCellValue which equals just the ChangedCellValue, if the first time, and it equals CurrentCellValue/ChangedCellValue if the second or consecutive time.

I had to use the Application.EnableEvents = False to keep the Private Sub Worksheet_SelectionChange from triggering immediately after the Private Sub Worksheet_Change and creating an endless loop.

I also had to add the new cell value to CurrentCellValue variable within the Private Sub Worksheet_Change routine for consecutive runs as the Private Sub Worksheet_SelectionChange isn't triggered again so long as the focus remains on column R's cell while choosing additional items from the drop-down menu.

I also added a clause that exited the sub if the cell is blank, so that it was possible to delete the value and to move the focus off that cell without the Worksheet_Change triggering and placing the value back in that cell again.

I don't know... Please let me know if there are any tweaks anybody sees that could improve this routine or error handling I could add (or a means of being able to manually edit this without getting really strange results).

As always, thanks much!

=======================================================

Public CurrentCellValue

=========================================================


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'If a Column R Cell Is Selected, Remember That Cell's Value In The CurrentCellValue Variable
    If ActiveCell.Column = 18 Then
        CurrentCellValue = Range("R" & ActiveCell.Row).Value
    End If

End Sub

=========================================================

Public Sub Worksheet_Change(ByVal Target As Range)

    Dim WatchRange As Range
    Dim IntersectRange As Range
    Dim StrRecordNumber As String
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim NewCellValue
    
    'When a Change Has Been Made in Column R Add The Former Cell Value To The New Cell Value
    Set WatchRange = Range("R" & ActiveCell.Row & ":" & "R" & ActiveCell.Row & "")
    Set IntersectRange = Intersect(Target, WatchRange)
    If Not (IntersectRange Is Nothing) Then
        If WatchRange = "" Then Exit Sub
        Application.EnableEvents = False
        CurrentCellValue = CurrentCellValue
        ChangedCellValue = Range("R" & ActiveCell.Row).Value
        If CurrentCellValue = "" Then
            NewCellValue = ChangedCellValue
        Else
            NewCellValue = CurrentCellValue & "/" & ChangedCellValue
        End If
        Cells(ActiveCell.Row, 18).Value = NewCellValue
        CurrentCellValue = Range("R" & ActiveCell.Row).Value
        Application.EnableEvents = True
    End If

End Sub

=========================================================

I then have Data Validation set on column R with the following settings set:

List
Ignore blank
In-cell dropdown
Source: =$AJ$5:$AJ$25

With no Input Message or Alert Error set.

=========================================================

My list in cells $AJ$5:$AJ$25 is as follows:

Fan
System Board
Motherboard
NIC
Network Card
Plastic
Power Issue
Catastrophic Damage
Beyond Economic Repair
Broken LCD
LCD Backlight
DC Port
Keyboard Issue
Touchpad Issue
Trackpad Issue
Battery Failure
Loose Screws
WiFi Issue
SSD Failure
HD Issue
Other