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

NessPJ · Nov 11, 2016

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

NessPJ · Nov 16, 2016

Rhodie72 said:
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

I don't see how that tutorial explains putting the data from a userform in my sheet when using dynamic objects?

NessPJ · Nov 16, 2016

@ Mr. Worf,

Okay i got your code to work now (I had to change the UserForm_Initialize to UserForm_Activate). But what i notice is that the value (caption) of the Checkbox i click is created immediatly in the Excel sheet when it is clicked.
This means i can click the value repeatedly and i could go on endlessly creating new entries. This is not desired functionality.

I would like the entries to be created after i click the Okay button on the Userform (so the user could never create infinite/multiple entries by accident).
Is that possible?

Here is how the code is looking at the moment:

Code:

Option Explicit


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


Private Sub UserForm_Activate()


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 = ThisWorkbook.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()
Set md = ThisWorkbook.Sheets("Database")
If unicbox Then _
md.Cells(md.Range("A" & Rows.Count).End(xlUp).Row + 1, 1) = unicbox.Caption ' transfer to sheet
End Sub

NessPJ · Nov 18, 2016

I got it!

All it took was deleting an "End For" which i had placed too soon in the routine.

Code:

Private Sub OKButton_Click()

Dim xv As Control
Dim LastRowDatabase As String

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



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


                     '### Every other value or action you want entered or executed can be placed here ###
                     
                     LastRowDatabase = LastRowDatabase + 1
                     
                 End If
             End If
         End If
     Next

Unload Me


End Sub

NessPJ · Nov 30, 2016

Hi all,

I have one last question about my Dynamic Objects on my Userform.

I use this code to generate a list of checkboxes (and output their generated name to a cell). Now i would like to use a piece of code inside my UserForm_Activate module to select one of these Checkboxes
after it has been generated. For some reason Excel is not letting me and i keep getting an Error 'Object required'.

This is the code i use for generating the dynamic checkboxes:

Rich (BB code):

Option Explicit


Public MutDatum, MutTijd, MutWeek, MutDag As String
Public MutDienst, MutTechnieker, MutTRow, MutTBox, MutActiviteit, MutTijdEenheden, MutOmschrijving, MutVervolgActie, MutVervolgActieCode As String


Private Sub UserForm_Activate()

Dim OmschrijvingWaarde, LastRowTechnieker, LastRowActiviteit, LastRowTag As String
Dim RegelnrRij, RegelnrGevonden 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

'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
Next i


i = 0

            MutTechnieker = ThisWorkbook.Sheets("Data Invoer").Range("AD" & RegelnrRij)
            MutTRow = Zoeken(MutTechnieker, "Masterdata", "C31", "C")
            MutTBox = ThisWorkbook.Sheets("Masterdata").Range("E" & MutTRow).Value
            MsgBox Me.MutTBox.Value


End Sub

Public Function Zoeken(ZoekWaarde, ZoekSheet, LaatsteCellVoorZoeken, KolomVoorZoeken As String)


'De volgende routine creeërt een functie en zoekt een ingegeven string van onderen naar boven in de tabel (zodat de meest recente wordt gevonden).
'Vervolgens wordt het Regelnummer (Rownumber) teruggemeld als gevonden waarde.


Dim StringRowNumber As Integer, LaatsteCellZoekVeld, code As String


    LaatsteCellZoekVeld = ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken, ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken).End(xlUp)).Row
      
    If ZoekWaarde = "" Then GoTo StopMetZoeken
    
    For StringRowNumber = LaatsteCellZoekVeld To 1 Step -1
         code = ThisWorkbook.Sheets(ZoekSheet).Range(KolomVoorZoeken & StringRowNumber)
         If code = ZoekWaarde Then
         Zoeken = StringRowNumber
            Exit For
         End If
    Next
  
StopMetZoeken:


End Function

As a test i try to report the Value of the checkbox (just to see if i am addressing the right box)...but this still gives me the error.

What am i doing wrong?

Worf · Dec 1, 2016

When I run the code, the variable “RegelnrRij” is empty, can you check that?

NessPJ · Dec 1, 2016

Worf said:
When I run the code, the variable “RegelnrRij” is empty, can you check that?

Ahh...i forgot to include that in my post, sorry.

RegelnrRij is given by the user through an InputBox.

Code:

InputBox RegelnrRij

Worf · Dec 4, 2016

This worked for me:

Code:

Public MutDatum, MutTijd, MutWeek, MutDag$, MutDienst, MutTechnieker, MutTRow, _
MutTBox, MutActiviteit, MutTijdEenheden, MutOmschrijving, MutVervolgActie, MutVervolgActieCode$

Private Sub UserForm_Activate()
Dim OmschrijvingWaarde, LastRowTechnieker, LastRowActiviteit, LastRowTag$
Dim RegelnrRij, RegelnrGevonden$
Dim curColumn       As Long, i               As Long
Dim chkBox          As MSForms.CheckBox, Optionbutton    As MSForms.Optionbutton
RegelnrRij = InputBox("regel", "enter value")
LastRowTechnieker = ThisWorkbook.Sheets("Masterdata").Range("B31").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 - 4))
    chkBox.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn)
    chkBox.Left = 12
    chkBox.Top = 8 + ((i - 4) * 16)
    chkBox.GroupName = "Technieker"
    ThisWorkbook.Sheets("Masterdata").Range("E" & i) = chkBox.Name
Next
i = 0
MutTechnieker = ThisWorkbook.Sheets("Data Invoer").Range("AD" & RegelnrRij)
MutTRow = Zoeken(MutTechnieker, "Masterdata", "C31", "C")
MutTBox = ThisWorkbook.Sheets("Masterdata").Range("E" & MutTRow)
MsgBox Me.Controls("CheckBox_" & MutTBox).Value
End Sub

Public Function Zoeken(ZoekWaarde, ZoekSheet, LaatsteCellVoorZoeken, KolomVoorZoeken$)
Dim StringRowNumber As Integer, LaatsteCellZoekVeld, Code$
LaatsteCellZoekVeld = ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken, _
ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken).End(xlUp)).Row
If ZoekWaarde = "" Then GoTo StopMetZoeken
For StringRowNumber = LaatsteCellZoekVeld To 1 Step -1
    Code = ThisWorkbook.Sheets(ZoekSheet).Range(KolomVoorZoeken & StringRowNumber)
    If Code = ZoekWaarde Then
        Zoeken = StringRowNumber
        Exit For
    End If
Next
StopMetZoeken:
End Function

NessPJ · Dec 5, 2016

Hello Mr. Worf,
Thanks again for your help and patience!

I changed the way i tried to address the Object on my userform and now it works! I added the following code:

Code:

Dim MutTchkBox      As MSForms.CheckBox

            Set MutTchkBox = Me.Controls(MutTBox)
            MutTchkBox.Value = True

Worf · Dec 9, 2016

You are welcome!

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

NessPJ

Active Member

NessPJ

Active Member

Excel Facts

NessPJ

Active Member

NessPJ

Active Member

NessPJ

Active Member

Worf

Well-known Member

NessPJ

Active Member

Worf

Well-known Member

NessPJ

Active Member

Worf

Well-known Member

Similar threads

Forum statistics

Share this page

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

Active Member

Active Member

Excel Facts

Active Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock