lundi 19 juin 2017

Excel VBA - Create array from variable number of userform checkboxes

I think this is less complicated than I am making it but being a novice with VBA I haven't been able to find the answer after a couple days of googling and playing with different code.

I have a macro that:

  1. Opens a file in whatever folder path is named
  2. Searches for specific text in the file to find the start of a specific section, i.e. A100
  3. Finds the end of this section, i.e. A110 (variable in length depending on the file)
  4. Copy and pastes the cells in this range to another specific range, i.e. O1:O10
  5. Populates a userform with a checkbox for each cell in this new variable length range

I now need the user to click which checkboxes they want and then the captions for these checkboxes to be saved as an array that I can then call on later in the macro.

i.e. if they clicked dog, cat, and bird from the checkboxes, the output would be dog,cat,bird

Because of the variable length of the range and number of checkboxes, I can't figure out how to have it loop through each one and concatenate the correct values.

I think there is probably a way to cut out the copy pasting of the values to populate the userform with also, but this was the only way I could figure out that part given the variable length of the range.

Below is the code that generates the userform after the range has been copy pasted.

Private Sub UserForm_Initialize()

Dim curColumn   As Long
Dim i           As Long
Dim codeRow     As Long
Dim chkBox      As msforms.CheckBox

curColumn = 15
codeRow = Range("O20").End(xlUp).Row

For i = 1 To codeRow
Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
chkBox.Caption = Worksheets(1).Cells(i, curColumn).Value
chkBox.Left = 5
chkBox.Top = 5 + ((i - 1) * 20)
Next i

End Sub




Aucun commentaire:

Enregistrer un commentaire