Userform

[Luke777]

Hi,

I'm not very good (at all with userforms).

I have a spreadsheet with names in A:A (variable number of names). Each name has a corresponding data point in B and C.

I would like to run a macro that opens up a userform with a drop down list of each name in A:A. Once a name is selected, press OK on the same userform. This will then run the rest of my macro which will use selectedName, dataPointB and dataCointC as variables. I would like to 'capture' the name and corresponding variables - I think this might be doable with a scripting.dictionary approach but I'm not sure if that's best (simple offset might be better after getting cell reference of name selection?)

Thanks

 

[dmt32]

Hi

If you have little understanding of Userforms then I would suggest that you have a look here: Create an Excel UserForm

for some basic guidance. Site provides step by step instructions in building simple userform & you can download sample workbook for free.

For the requirement you have outlined in your post then following as a suggestion *MAY* be a start in the right direction for you

Place all codes in your USERFORM code page

'declare module level variables

'specify variables datatype (change as required)
Dim DataPointB      As Variant, DataPointC As Variant
Dim SelectedName    As String

'record index (integer datatype - long)
Dim RecordIndex     As Long

'data array (must be Variant)
Dim arrData         As Variant

Private Sub ComboBox1_Change()
   
    'get array index using listindex property of the control
    RecordIndex = Me.ComboBox1.ListIndex + 1
    If RecordIndex = 0 Then Exit Sub
   
    'populate variables from array elements
    SelectedName = arrData(RecordIndex, 1)
    DataPointB = arrData(RecordIndex, 2)
    DataPointC = arrData(RecordIndex, 3)
     
End Sub

Private Sub UserForm_Initialize()
    Dim ws          As Worksheet
   
    'set ws object variable to required worksheet (change name as required)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    'data range
    With ws.Range("A1").CurrentRegion
        'populate array - (excludes header row)
        arrData = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
   
    'slice arrData array to populate control with names in column(1)
    Me.ComboBox1.List = Application.Index(arrData, , 1)
   
End Sub

Code assumes that your data is in a range & row 1 is a header row

Note the variables placed at the top of the procedures

These must be placed at very TOP of your userforms code page OUTSIDE any procedure.

By doing this, makes them available to all procedures in the userforms code page.


Hope Helpful

Dave

 

[Luke777]

Hi

If you have little understanding of Userforms then I would suggest that you have a look here: Create an Excel UserForm

for some basic guidance. Site provides step by step instructions in building simple userform & you can download sample workbook for free.

For the requirement you have outlined in your post then following as a suggestion *MAY* be a start in the right direction for you

Place all codes in your USERFORM code page

'declare module level variables

'specify variables datatype (change as required)
Dim DataPointB      As Variant, DataPointC As Variant
Dim SelectedName    As String

'record index (integer datatype - long)
Dim RecordIndex     As Long

'data array (must be Variant)
Dim arrData         As Variant

Private Sub ComboBox1_Change()
  
    'get array index using listindex property of the control
    RecordIndex = Me.ComboBox1.ListIndex + 1
    If RecordIndex = 0 Then Exit Sub
  
    'populate variables from array elements
    SelectedName = arrData(RecordIndex, 1)
    DataPointB = arrData(RecordIndex, 2)
    DataPointC = arrData(RecordIndex, 3)
    
End Sub

Private Sub UserForm_Initialize()
    Dim ws          As Worksheet
  
    'set ws object variable to required worksheet (change name as required)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
  
    'data range
    With ws.Range("A1").CurrentRegion
        'populate array - (excludes header row)
        arrData = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
  
    'slice arrData array to populate control with names in column(1)
    Me.ComboBox1.List = Application.Index(arrData, , 1)
  
End Sub

Code assumes that your data is in a range & row 1 is a header row

Note the variables placed at the top of the procedures

These must be placed at very TOP of your userforms code page OUTSIDE any procedure.

By doing this, makes them available to all procedures in the userforms code page.


Hope Helpful

Dave

Thanks for this.

I've had a look through some of the materials you suggested, thanks for pointing them out.

I've got the code you've given working - is the userform meant to hang around after a selection is made? I tried getting it to simply debug print the results once a selection was made but the box just stays at the forefront.

Thanks

 

[dmt32]

I've got the code you've given working - is the userform meant to hang around after a selection is made? I tried getting it to simply debug print the results once a selection was made but the box just stays at the forefront.

Not sure what you mean by "hang around"? Code is using and array to populate your variables so should not cause any performance issues

If cannot resolve, suggest that you share the rest of your code once selection is made

Dave

 

[Luke777]

Not sure what you mean by "hang around"? Code is using and array to populate your variables so should not cause any performance issues

If cannot resolve, suggest that you share the rest of your code once selection is made

Dave

by hang around I meant the box itself sits in view, doesn't seem to pass the selection to any variables according to the locals window

 

[dmt32]

by hang around I meant the box itself sits in view, doesn't seem to pass the selection to any variables according to the locals window

based on combobox selection, suggested code populates your variables from the elements of an array which itself is populated from specified worksheet range - code does work ok for me.

beyond this, you have not shared what else it is that you are doing so little difficult to figure out your issue - If can place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it - will have a look.

Dave