[VBA] Please help me with my UserForm (Scrollbar value and putting dynamic objects result in Cell)

NessPJ

Active Member
Joined
May 10, 2011
Messages
414
Office Version
  1. 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:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hallo

The example below relates to item #2; I will be back tomorrow for item #1…

Code:
Private Sub CommandButton1_Click()
If CStr(Me.SpinButton1.Value) = Me.TextBox1.Text Then
    Sheets("output").[f35] = Me.SpinButton1.Value       ' transfer value to sheet
    Unload Me
Else
    Me.TextBox1.SetFocus
    Me.TextBox1.SelStart = 0
    Me.TextBox1.SelLength = Len(Me.TextBox1.Text)
End If
End Sub


Private Sub SpinButton1_Change()
TextBox1.Text = Me.SpinButton1.Value
End Sub


Private Sub TextBox1_Change()
Dim newval%
newval = Val(Me.TextBox1.Text)
If newval >= Me.SpinButton1.Min And newval <= Me.SpinButton1.Max Then _
Me.SpinButton1.Value = newval
End Sub


Private Sub TextBox1_Enter()
Me.TextBox1.SelStart = 0
Me.TextBox1.SelLength = Len(Me.TextBox1.Text)          ' selects all text
End Sub


Private Sub UserForm_Initialize()
Me.TextBox1.Text = "1"
End Sub
 
Upvote 0
Hello Mr. Worf,

Thanks for your reply so far! With your help i was able to finish Item #2.

I ended up making a simple validation and also pointing the SpinButton to the TextBox made it work! Here's the code i used:

Code:
Option Explicit


Private Sub UserForm_Initialize()


Dim TijdEenheidWaarde As Integer

TijdEenheidWaarde = 1


Me.TijdEenheid = TijdEenheidWaarde

End Sub

Private Sub TijdEenheidButton_SpinUp()


Dim TijdEenheidWaarde As Integer


On Error Resume Next


Me.TijdEenheid = Me.TijdEenheid + 1


End Sub


Private Sub TijdEenheidButton_SpinDown()


Dim TijdEenheidWaarde As Integer


On Error Resume Next


If Not TijdEenheid.Value <= 1 Then
Me.TijdEenheid = Me.TijdEenheid - 1
End If


End Sub
 
Upvote 0
Hello all,

I found a piece of code which allows me to check if any of the OptionButtons inside a Group on my userform is selected.
This works fine.

However...i would also like to do a similar check for a Group of CheckBoxes (which works differently, since multiple checkboxes could be selected).

Can anyone please point me in the right direction, how i can check all the CheckBoxes inside a Group to see IF one of them is True?

Code:
Dim x As Control  
        For Each x In Me.Controls                   'Loop through ALL the controls on the UserForm.
            If InStr(x.Name, "Option") Then         'Check to see if "Option" is in the Name of each control.
             If x.GroupName = "Activiteit" Then     'Check Group name.
                 If Not x.Value = True Then         'Check the status of the OptionButton.
                    MsgBox "Kies een activiteit!", vbExclamation, "Ongeldige invoer!"
                    x.SetFocus
                    Exit Sub
                    Exit For
                 End If
             End If
         End If
     Next
 
Upvote 0
a) See if the example below helps.
b) I assume you still need a solution for item #1, correct?

Code:
Private Sub CommandButton2_Click()
Dim x As Control, L$, c%
L = "": c = 0
For Each x In Me.Controls
    If InStr(x.Name, "Check") Then
        If x.GroupName = "cbgroup" Then
            If x.Value = True Then
                L = L & x.Name & vbLf
                c = c + 1
            End If
        End If
    End If
Next
MsgBox c & " check boxes selected:" & vbLf & L
End Sub


Private Sub UserForm_Initialize()
Me.CheckBox2.GroupName = "cbgroup"
Me.CheckBox3.GroupName = "cbgroup"
Me.CheckBox4.GroupName = "cbgroup"
End Sub
 
Upvote 0
This example is a proposed solution for item #1. Note that a single click routine handles all check boxes; this is achieved with a collection.

Code:
' UserForm module
Public WithEvents unicbox As MSForms.CheckBox
Dim unifCBoxes As Collection, md As Worksheet
                                                ' based on code by mikerickson
Private Sub unicbox_Click()
If unicbox Then _
md.Cells(md.Range("a" & Rows.Count).End(xlUp).Row + 1, 1) = unicbox.Caption ' transfer to sheet
End Sub


Private Sub UserForm_Activate()
Dim subUF As Object, LastRowTechnieker, LastRowActiviteit, curColumn&, i&
Dim chkBox As MSForms.CheckBox, Optionbutton    As MSForms.Optionbutton
LastRowTechnieker = md.[B31].End(xlUp).Row
LastRowActiviteit = md.Range("B51").End(xlUp).Row
curColumn = 3                                       'Set your column index here
For i = 5 To LastRowTechnieker
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = md.Cells(i, curColumn)
    chkBox.Left = 12
    chkBox.Top = 8 + ((i - 4) * 16)
    Set subUF = UserForms.Add(Me.Name)
Set subUF.unicbox = chkBox
unifCBoxes.Add Item:=subUF, Key:=CStr(UserForms.Count)
Next
curColumn = 3                                       'Set your column index here
For i = 35 To LastRowActiviteit
    Set Optionbutton = Me.Controls.Add("Forms.OptionButton.1", "OptionButton_" & i)
    Optionbutton.Caption = md.Cells(i, curColumn)
    Optionbutton.Left = 132
    Optionbutton.Top = 8 + ((i - 34) * 16)
    Optionbutton.Width = 196
Next
End Sub


Private Sub UserForm_Terminate()
Dim oneUF As Object
For Each oneUF In unifCBoxes
    Unload oneUF
    Set oneUF = Nothing
Next
Set unifCBoxes = Nothing
End Sub


Private Sub UserForm_Initialize()
Set md = Sheets("masterdata")
Set unifCBoxes = New Collection
End Sub
 
Upvote 0
Thanks a lot for the help Mr. Worf!

I can get the group of checkboxes validated now through VBA :)
Code:
Dim x, x1 As Control, L$, c%   

        For Each x1 In Me.Controls                      'Loop through ALL the controls on the UserForm.
            If InStr(x1.Name, "CheckBox") Then          'Check to see if "CheckBox" is in the Name of each control.
                If x1.GroupName = "Technieker" Then     'Check Group name.
               '  If Not x1.Value > 0 Then              'Check the status of the OptionButton.
                    'x1.SetFocus
                    'Exit Sub
                  Exit For
               'End If
             End If
         End If
     Next
    


    L = "": c = 0
    For Each x In Me.Controls
            If InStr(x.Name, "Check") Then
                    If x.GroupName = "Technieker" Then
                            If x.Value = True Then
                                L = L & x.Name & vbLf
                                c = c + 1
                            End If
                    End If
            End If
    Next


    If c = 0 Then
        MsgBox "Kies minimaal één technieker!", vbExclamation, "Ongeldige invoer!"
        x1.SetFocus
        Exit Sub
    End If
    
    x = 0

For some reason, my Optionbuttons are not validating correctly though (value stays 0 or False, even when an Optionbutton is selected):
Code:
    For Each x In Me.Controls                       'Loop through ALL the controls on the UserForm.
         If InStr(x.Name, "Option") Then            'Check to see if "Option" is in the Name of each control.
             If x.GroupName = "Activiteit" Then     'Check Group name.
                 If Not x.Value = True Then         'Check the status of the OptionButton.
                     'MsgBox x
                     MsgBox "Kies een activiteit!", vbExclamation, "Ongeldige invoer!"
                     x.SetFocus
                    Exit Sub
                    Exit For
                 End If
             End If
         End If
     Next

Any idea what i might be doing wrong?
 
Last edited:
Upvote 0
Okay, i now have my validation working for the Group of CheckBoxes and the Group of OptionButtons using this code:

Code:
Private Sub OKButton_Click()


'Validatie van invoer wanneer op OK wordt geklikt:
'=================================================
Dim x, x1, x2, x3 As Control, L$, c%                    'Deze  variabelen worden gedefinieerd voor uitvoeren van de onderstaande routine(s).




'Valideer invoer geldige Technieker (er moet minimaal 1 Technieker geselecteerd zijn):


        For Each x1 In Me.Controls                      'Loop through ALL the controls on the UserForm.
            If InStr(x1.Name, "CheckBox") Then          'Check to see if "CheckBox" is in the Name of each control.
                If x1.GroupName = "Technieker" Then     'Check Group name.
                  Exit For                              'Variable x1 now contains the ObjectName of the first CheckBox in the specified Group.
             End If
         End If
     Next
    


    L = "": c = 0
    For Each x In Me.Controls
            If InStr(x.Name, "Check") Then
                    If x.GroupName = "Technieker" Then
                            If x.Value = True Then
                                L = L & x.Name & vbLf
                                c = c + 1                   'MsgBox c & " check boxes selected:" & vbLf & L
                            End If
                    End If
            End If
    Next


    If c = 0 Then
        MsgBox "Kies minimaal één technieker!", vbExclamation, "Ongeldige invoer!"
        x1.SetFocus
        Exit Sub
    End If




'Valideer invoer geldige Activiteit (er moet 1 Activiteit geselecteerd zijn):


        For Each x2 In Me.Controls                      'Loop through ALL the controls on the UserForm.
            If InStr(x2.Name, "Option") Then            'Check to see if "Option" is in the Name of each control.
                If x2.GroupName = "Activiteit" Then     'Check Group name.
                  Exit For                              'Variable x2 now contains the ObjectName of the first OptionButton in the specified Group.
             End If
         End If
     Next
 
    L = "": c = 0
    For Each x3 In Me.Controls
            If InStr(x3.Name, "Option") Then
                    If x3.GroupName = "Activiteit" Then
                            If x3.Value = True Then
                                L = L & x3.Name & vbLf
                                c = c + 1
                            End If
                    End If
            End If
    Next


    If c = 0 Then
        MsgBox "Kies een activiteit!", vbExclamation, "Ongeldige invoer!"
        x2.SetFocus
        Exit Sub
    End If

Not sure if i'm doing this in the most 'optimised' manner but its working. :)
 
Upvote 0
Dear Mr. Worf,

I have interpreted your code suggestion for Item #1 in my existing VBA project. It compiles, but when i try to execute the Userform it halts on an error "Automation Error"

This is my entire VBA code for the UserForm now:
Code:
Option Explicit


Public WithEvents unicbox As MSForms.CheckBox
Dim unifCBoxes As Collection, md As Worksheet


Private Sub UserForm_Initialize()


Dim LastRowTechnieker, LastRowActiviteit As String
Dim TijdEenheidWaarde As Integer
Dim subUF As Object


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


Set md = Sheets("Database")
Set unifCBoxes = New Collection


'Vooraf wordt de verwachte dienst geselecteerd kijkend naar het huidige tijdstip:


 If Hour(Now) = 0 Then
     Me.DienstNacht.Value = 1
     Me.DienstVroeg.Value = 0
     Me.DienstLaat.Value = 0
 End If


 If Hour(Now) > 0 Then
     Me.DienstNacht.Value = 1
     Me.DienstVroeg.Value = 0
     Me.DienstLaat.Value = 0
 End If


 If Hour(Now) > 6 Then
     Me.DienstNacht.Value = 0
     Me.DienstVroeg.Value = 1
     Me.DienstLaat.Value = 0
 End If


 If Hour(Now) > 14 Then
     Me.DienstNacht.Value = 0
     Me.DienstVroeg.Value = 0
     Me.DienstLaat.Value = 1
 End If


 If Hour(Now) > 22 Then
     Me.DienstNacht.Value = 1
     Me.DienstVroeg.Value = 0
     Me.DienstLaat.Value = 0
 End If


TijdEenheidWaarde = 1


Me.TijdEenheid = TijdEenheidWaarde


'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 - 4))
    chkBox.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn).Value
    chkBox.Left = 12
    chkBox.Top = 8 + ((i - 4) * 16)
    chkBox.GroupName = "Technieker"
    ThisWorkbook.Sheets("Masterdata").Range("E" & i) = chkBox.Name
    Set subUF = UserForms.Add(Me.Name)
    Set subUF.unicbox = chkBox
    unifCBoxes.Add Item:=subUF, Key:=CStr(UserForms.Count)
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 - 34))
    Optionbutton.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn).Value
    Optionbutton.Left = 130
    Optionbutton.Top = 8 + ((i - 34) * 16)
    Optionbutton.Width = 164
    Optionbutton.GroupName = "Activiteit"
    ThisWorkbook.Sheets("Masterdata").Range("D" & i) = Optionbutton.Name
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()


Dim oneUF As Object
For Each oneUF In unifCBoxes
    Unload oneUF
    Set oneUF = Nothing
Next
Set unifCBoxes = Nothing


Unload Me


End Sub


Private Sub UserForm_Terminate()


Dim oneUF As Object
For Each oneUF In unifCBoxes
    Unload oneUF
    Set oneUF = Nothing
Next
Set unifCBoxes = Nothing
End Sub




Private Sub OKButton_Click()


'=====================================================
'= Validatie van invoer wanneer op OK wordt geklikt: =
'=====================================================


Dim x, x1, x2, x3 As Control, L$, c%                    'Deze  variabelen worden gedefinieerd voor uitvoeren van de onderstaande routine(s).
Dim LastRowDatabase


LastRowDatabase = ThisWorkbook.Sheets("Database").Range("A65534").End(xlUp).Row + 1


'Valideer invoer geldige Technieker (er moet minimaal 1 Technieker geselecteerd zijn):


        For Each x1 In Me.Controls                      'Loop through ALL the controls on the UserForm.
            If InStr(x1.Name, "CheckBox") Then          'Check to see if "CheckBox" is in the Name of each control.
                If x1.GroupName = "Technieker" Then     'Check Group name.
                  Exit For                              'Variable x1 now contains the ObjectName of the first CheckBox in the specified Group.
             End If
         End If
     Next
    


    L = "": c = 0
    For Each x In Me.Controls
            If InStr(x.Name, "Check") Then
                    If x.GroupName = "Technieker" Then
                            If x.Value = True Then
                                L = L & x.Name & vbLf
                                c = c + 1                   'MsgBox c & " check boxes selected:" & vbLf & L
                            End If
                    End If
            End If
    Next


    If c = 0 Then
        MsgBox "Kies minimaal één technieker!", vbExclamation, "Ongeldige invoer!"
        x1.SetFocus
        Exit Sub
    End If




'Valideer invoer geldige Activiteit (er moet 1 Activiteit geselecteerd zijn):


        For Each x2 In Me.Controls                      'Loop through ALL the controls on the UserForm.
            If InStr(x2.Name, "Option") Then            'Check to see if "Option" is in the Name of each control.
                If x2.GroupName = "Activiteit" Then     'Check Group name.
                  Exit For                              'Variable x2 now contains the ObjectName of the first OptionButton in the specified Group.
             End If
         End If
     Next
 
    L = "": c = 0
    For Each x3 In Me.Controls
            If InStr(x3.Name, "Option") Then
                    If x3.GroupName = "Activiteit" Then
                            If x3.Value = True Then
                                L = L & x3.Name & vbLf
                                c = c + 1
                            End If
                    End If
            End If
    Next


    If c = 0 Then
        MsgBox "Kies een activiteit!", vbExclamation, "Ongeldige invoer!"
        x2.SetFocus
        Exit Sub
    End If




'Valideer invoer geldige Dienst (er moet 1 Dienst geselecteerd zijn):


    If Me.DienstNacht.Value = 0 And Me.DienstVroeg.Value = 0 And Me.DienstLaat.Value = 0 Then
        MsgBox "Kies een dienst!", vbExclamation, "Ongeldige invoer!"
        Me.DienstVroeg.SetFocus
        Exit Sub
    End If
    
'Valideer invoer geldige TijdEenheid (er moet een geldig getal ingegeven zijn):


    If Not IsNumeric(TijdEenheid.Value) Or TijdEenheid.Value = 0 Or TijdEenheid.Value < 0 Then
        MsgBox "Voer een geldige tijd eenheid in!", vbExclamation, "Ongeldige invoer!"
        Me.TijdEenheid.SetFocus
        Exit Sub
    End If
    
'========================================
'= UserForm verwerken en data invoeren: =
'========================================




'Geselecteerde Technieker bepalen en invoeren:


'        For Each x In Me.Controls                   'Loop through ALL the controls on the UserForm.
'            If InStr(x.Name, "CheckBox") Then       'Check to see if "CheckBox" is in the Name of each control.
'             If x.GroupName = "Technieker" Then     'Check Group name.
'                 If x.Value = True Then             'Check the status of the CheckBox.
'                     ThisWorkbook.Sheets("Database").Range("A" & LastRowDatabase) = x.Caption
'                     LastRowDatabase = ThisWorkbook.Sheets("Database").Range("A65534").End(xlUp).Row + 1
'                     'ThisWorkbook.Sheets("Database").Range("J2") = x.Caption
'                     Exit For
'                 End If
'             End If
'         End If
'     Next
     


'Geselecteerde Activiteit bepalen en invoeren:


        For Each x In Me.Controls                   'Loop through ALL the controls on the UserForm.
            If InStr(x.Name, "Option") Then         'Check to see if "Option" is in the Name of each control.
             If x.GroupName = "Activiteit" Then     'Check Group name.
                 If x.Value = True Then             'Check the status of the OptionButton.
                     ThisWorkbook.Sheets("Database").Range("L2") = x.Caption
                     Exit For
                 End If
             End If
         End If
     Next




'Geselecteerde Dienst bepalen en invoeren:


        For Each x In Me.Controls                   'Loop through ALL the controls on the UserForm.
            If InStr(x.Name, "Dienst") Then         'Check to see if "Dienst" is in the Name of each control.
             If x.GroupName = "Dienst" Then         'Check Group name.
                 If x.Value = True Then             'Check the status of the OptionButton.
                     ThisWorkbook.Sheets("Database").Range("I2") = x.Caption
                     Exit For
                 End If
             End If
         End If
     Next




'Gekozen TijdeenHeid invoeren:
ThisWorkbook.Sheets("Database").Range("H2") = TijdEenheid.Value


    
    'ThisWorkbook.Sheets("ROPO").Range("M7") = ComboBox1.Text


    'ThisWorkbook.Sheets("ROPO").Range("M9") = ComboBox2.Text






' Einde routine


MsgBox "Het Invoer formulier is succesvol doorlopen!", vbDefaultButton1, "Routine afgerond"




Unload Me


End Sub


Private Sub TijdEenheidButton_SpinUp()


Dim TijdEenheidWaarde As Integer


On Error Resume Next


Me.TijdEenheid = Me.TijdEenheid + 1


End Sub


Private Sub TijdEenheidButton_SpinDown()


Dim TijdEenheidWaarde As Integer


On Error Resume Next


If Not TijdEenheid.Value <= 1 Then
Me.TijdEenheid = Me.TijdEenheid - 1
End If


End Sub


Private Sub unicbox_Click()
If unicbox Then _
md.Cells(md.Range("A" & Rows.Count).End(xlUp).Row + 1, 1) = unicbox.Caption ' transfer to sheet
End Sub

Could you perhaps tell me what i am doing wrong?
 
Last edited:
Upvote 0
If you are doing advanced things like this in your worksheet you should really consider using the forms built into the VBE. it removes the actions from the sheets and puts them squarely in the code itself. This video explains it well. If you wish to learn more about forms then you can take a look at the series here
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top