NessPJ
Active Member
- Joined
- May 10, 2011
- Messages
- 420
- Office Version
- 365
Hello guys,
After some reading around i have made a UserForm for my excel sheet which also contains some dynamic checkboxes/optionbuttons.
This is new terrain for me and i'm running into a few issues at the moment, of which i don't know what to do....
1. How can i output the results by the user from the dynamic checkboxes and optionbuttons onto my Excel sheet?
For every checkbox that the user checks i would like to write a line of data onto the first available row in my Excel sheet.
To make it a little more complexe: the dynamic checkboxes and optionbuttons actually contain the "description" (or caption) of the actual code or value which i would like to output onto my excel sheet. These descriptions are read from a Masterdata sheet in my excel file and the code or value that should be put out is in the same table in the column right next to it).
2. I would like to add a value on the userform which the user can input in a text field (a single number) but which also contains and increase/decrease
button for this field. So the user can either input a number or start from default value "1" and increase from there.
I have googled this for almost an hour, but i can not for the live of my find out how to do this!
Here is the code i have so far, for the userform:
After some reading around i have made a UserForm for my excel sheet which also contains some dynamic checkboxes/optionbuttons.
This is new terrain for me and i'm running into a few issues at the moment, of which i don't know what to do....
1. How can i output the results by the user from the dynamic checkboxes and optionbuttons onto my Excel sheet?
For every checkbox that the user checks i would like to write a line of data onto the first available row in my Excel sheet.
To make it a little more complexe: the dynamic checkboxes and optionbuttons actually contain the "description" (or caption) of the actual code or value which i would like to output onto my excel sheet. These descriptions are read from a Masterdata sheet in my excel file and the code or value that should be put out is in the same table in the column right next to it).
2. I would like to add a value on the userform which the user can input in a text field (a single number) but which also contains and increase/decrease
button for this field. So the user can either input a number or start from default value "1" and increase from there.
I have googled this for almost an hour, but i can not for the live of my find out how to do this!
Here is the code i have so far, for the userform:
Code:
Option Explicit
Private Sub UserForm_Initialize()
Dim LastRowTechnieker, LastRowActiviteit As String
Dim curColumn As Long
Dim i As Long
Dim chkBox As MSForms.CheckBox
Dim Optionbutton As MSForms.Optionbutton
LastRowTechnieker = ThisWorkbook.Sheets("Masterdata").Range("B31").End(xlUp).Row
LastRowActiviteit = ThisWorkbook.Sheets("Masterdata").Range("B51").End(xlUp).Row
'Lijst met Technieker Checkboxes wordt dynamisch opgebouwd.
curColumn = 3 'Set your column index here
For i = 5 To LastRowTechnieker
Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
chkBox.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn).Value
chkBox.Left = 12
chkBox.Top = 8 + ((i - 4) * 16)
Next i
i = 0
'Lijst met Activiteiten Checkboxes wordt dynamisch opgebouwd.
curColumn = 3 'Set your column index here
For i = 35 To LastRowActiviteit
Set Optionbutton = Me.Controls.Add("Forms.OptionButton.1", "OptionButton_" & i)
Optionbutton.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn).Value
Optionbutton.Left = 132
Optionbutton.Top = 8 + ((i - 34) * 16)
Optionbutton.Width = 196
Next i
'Dit is een (test) box om snel variabelen te testen/controleren:
'MsgBox "i is now: " & i, , "Value i"
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub OKButton_Click()
MsgBox "You clicked Ok", vbDefaultButton1, "Ok button was clicked"
Unload Me
End Sub