Hi I am not experienced in VBA so please if possible respond in context.
I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}
My general module contains the following
Option Explicit
Public bResponse As Boolean
Public sComboVal As String
Sub Get_sComboVal
sComboVal = "Item1"
UserForm1.Show
If bResponse = False Then GoTo End_Subroutine
Unload UserForm1
' Various code inserted here
End_Subroutine:
' Various code inserted here
End Sub 'Get_sComboVal
UserForm1 contains just four objects:
CommandButton1 displays "OK"
CommandButton2 displays "Cancel"
ComboBox1
Label1 describes ComboBox1
What I am trying to achieve is this:
If at any time the user clicks "Cancel", the UserForm1 is unloaded and bResponse is returned False. Otherwise:
User clicks the dropdown arrow on the ComboBox and a list appears containing the values in MyArray.
User selects a value from that drop down list and confirms by clicking "OK".
That newly selected value is then assigned to the variable sComboVal
And then the routine in the general module that called the userform undertakes various actions based on the newly assigned value of sComboVal.
So far, all I have managed to achieve in the code behind the userform is to define what happens when the user clicks OK or cancel, thus:
Option Explicit
Private Sub CommandButton1_Click()
bResponse = True
UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
bResponse = False
UserForm1.ComboBox1.Value = sComboVal 'not sure that this is correct
Unload UserForm1
End Sub
At this point I get lost, that is to say programming the code for the ComboBox1,
(a) so that it lists the values of MyArray, and
(b) to assign to sComboVal the value selected from that list by the user.
Can someone please help me with these last steps (and correct any obvious errors so far)?
Thanks
Bookmarks