I have a two-dimensional table that holds "true" and "false" values. When a selection is made from the combo box (the one showing "ranger" in the picture), it looks through the two-dimensional table and finds which races are able to be that class and populates a second combo box (the blank one in the picture) with those values.
The issue i am having, is to get the formula working in the VB code like I did in a cell within the worksheet. Any kind of help would be most appreciated.
Below is a picture of my worksheet (sheet2) and the VB code.
Private Sub ComboBoxClasses_Change()
Dim PossibleRaces As String
Dim PossibleRacesFormula As String
Dim PossibleRacesSplit As Variant
Dim SC As String ' Selected Class
SC = Chr(34) & Me.ComboBoxClasses.Value & Chr(34)
PossibleRacesFormula = "=IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!H2, Sheet2!G2:N2, 0), FALSE), Sheet2!H2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!I2, Sheet2!G2:N2, 0), FALSE), Sheet2!I2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!J2, Sheet2!G2:N2, 0), FALSE), Sheet2!J2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!K2, Sheet2!G2:N2, 0), FALSE), Sheet2!K2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!L2, Sheet2!G2:N2, 0), FALSE), Sheet2!L2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!M2, Sheet2!G2:N2, 0), FALSE), Sheet2!M2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!N2, Sheet2!G2:N2, 0), FALSE), Sheet2!N2, """")"
PossibleRaces = CStr(Evaluate(PossibleRacesFormula))
PossibleRacesSplit = Split(PossibleRaces, "|")
InitializeComboBoxRaces (PossibleRacesSplit)
End Sub
Private Sub Worksheet_Activate()
InitializeComboBoxClasses
End Sub
Private Sub InitializeComboBoxClasses()
Me.ComboBoxClasses.Clear
Dim rngClasses As Range
For Each rngClasses In Worksheets("Sheet2").Range("CharacterClasses")
Me.ComboBoxClasses.AddItem rngClasses.Value
Next rngClasses
End Sub
Private Sub InitializeComboBoxRaces(PossibleRacesSplit As Variant)
Me.ComboBoxRaces.Clear
Dim rngRaces As Integer
For rngRaces = LBound(PossibleRacesSplit) To UBound(PossibleRacesSplit)
Me.ComboBoxRaces.AddItem PossibleRacesSplit(rngRaces)
Next rngRaces
End Sub
The issue i am having, is to get the formula working in the VB code like I did in a cell within the worksheet. Any kind of help would be most appreciated.
Below is a picture of my worksheet (sheet2) and the VB code.
Private Sub ComboBoxClasses_Change()
Dim PossibleRaces As String
Dim PossibleRacesFormula As String
Dim PossibleRacesSplit As Variant
Dim SC As String ' Selected Class
SC = Chr(34) & Me.ComboBoxClasses.Value & Chr(34)
PossibleRacesFormula = "=IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!H2, Sheet2!G2:N2, 0), FALSE), Sheet2!H2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!I2, Sheet2!G2:N2, 0), FALSE), Sheet2!I2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!J2, Sheet2!G2:N2, 0), FALSE), Sheet2!J2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!K2, Sheet2!G2:N2, 0), FALSE), Sheet2!K2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!L2, Sheet2!G2:N2, 0), FALSE), Sheet2!L2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!M2, Sheet2!G2:N2, 0), FALSE), Sheet2!M2 & ""|"", """") & IF(VLOOKUP(" & SC & ", Sheet2!G3:N12, MATCH(Sheet2!N2, Sheet2!G2:N2, 0), FALSE), Sheet2!N2, """")"
PossibleRaces = CStr(Evaluate(PossibleRacesFormula))
PossibleRacesSplit = Split(PossibleRaces, "|")
InitializeComboBoxRaces (PossibleRacesSplit)
End Sub
Private Sub Worksheet_Activate()
InitializeComboBoxClasses
End Sub
Private Sub InitializeComboBoxClasses()
Me.ComboBoxClasses.Clear
Dim rngClasses As Range
For Each rngClasses In Worksheets("Sheet2").Range("CharacterClasses")
Me.ComboBoxClasses.AddItem rngClasses.Value
Next rngClasses
End Sub
Private Sub InitializeComboBoxRaces(PossibleRacesSplit As Variant)
Me.ComboBoxRaces.Clear
Dim rngRaces As Integer
For rngRaces = LBound(PossibleRacesSplit) To UBound(PossibleRacesSplit)
Me.ComboBoxRaces.AddItem PossibleRacesSplit(rngRaces)
Next rngRaces
End Sub