Combo Boxes in User Form Keep repeating the options as I clear Them with Command Button

Y

Yeft

New Member
Joined
Jan 6, 2023
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hello There!
I'm a VBA Beginner and I'm struggling with a code in my User Form.
Every Time I hit a button to clear all the text Boxes and Combo boxes to clear them out for adding fresh data,
then it keeps populating (Adding up) with same options every combo Box. Is there Any one who could help me on
how to correct this issue or an improved code?

1715186739505.png
1715186816963.png



EDIT: I guess, I may have to rewrite the Subroutine "Clear" as well....?
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Yeft said:
Hello There!
I'm a VBA Beginner and I'm struggling with a code in my User Form.
Every Time I hit a button to clear all the text Boxes and Combo boxes to clear them out for adding fresh data,
then it keeps populating (Adding up) with same options every combo Box. Is there Any one who could help me on
how to correct this issue or an improved code?

View attachment 111127View attachment 111128


EDIT: I guess, I may have to rewrite the Subroutine "Clear" as well...
Click to expand...
…just pasting my code in case some one could fix it :

Private Sub UserForm1_Initialize()
Dim cntr As Integer

control21.Value = Application.UserName
control5 = Format(Now(), "dd/mmm/yyyy")
control22 = Format(Now(), "dd/mmm/yyyy")

cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ")) ' Method 1 for Combo Box
For i = 1 To cntr
Me.control1.AddItem Cells(i, 36)

Next i

Me.control2.AddItem "6" 'Method 2 for Combo Box
Me.control2.AddItem "8"
Me.control2.AddItem "10"
Me.control2.AddItem "12"
Me.control2.AddItem "14"
Me.control2.AddItem "16"
Me.control2.AddItem "XS"
Me.control2.AddItem "M"
Me.control2.AddItem "L"
Me.control2.AddItem "XL"

With Me.control15 'Method 3 for Combo Box
.AddItem "PAUL"
.AddItem "MIKE"
End With

End Sub


Thanks
 
Upvote 0
this looks like the routine only to "add" to your combo boxes.

Whats the button doing that you press "to clear out all the data " ?

Maybe something like
VBA Code: 
unload.me
can remove your combo box to start again ? if that helps somewhere in your code ?

cheers
Rob
 
Upvote 0
Hi,

The code you are sharing is your userforms initialize event which is used for code that prepares your userform for use before it is displayed.

However, your code will not do anything as you have made common error of renaming the event to that of your userform name.

You MUST not rename any event codes – it is ALWAYS UserForm_Initialize regardless of your userforms name.


See if this update to your code helps but if not, share the code that is assigned to your button

Place code in your userforms code page

Code: 
Private Sub UserForm_Initialize()
    Dim cntr        As Long
    
    Control21.Value = Application.UserName
    Control5 = Format(Now(), "dd/mmm/yyyy")
    Control22 = Format(Now(), "dd/mmm/yyyy")
    
    cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ"))
    
    Me.Control1.List = Cells(1, 36).Resize(cntr).Value
    
    Me.Control2.List = Array(6, 8, 10, 12, 14, 16, "XS", "M", "L", "XL")
    
    Me.Control15.List = Array("PAUL", "MIKE")
    
End Sub

I would also suggest that you give your controls meaningful names that follow a recognised Naming Convention

Hope helpful

Dave
 
Upvote 0
RobP said:
this looks like the routine only to "add" to your combo boxes.

Whats the button doing that you press "to clear out all the data " ?

Maybe something like
VBA Code: 
unload.me
can remove your combo box to start again ? if that helps somewhere in your code ?

cheers
Rob
Click to expand...
The "Clear" Button's job is merely to delete all text boxes and combo boxes content in order to start adding new Data to them.
please see the subroutine below:

Private Sub cmdClear_Click()

txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
'Me.control5 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
'Me.control21 = ""
'Me.control22 = ""

Call UserForm1_Initialize

MsgBox "Add New Record"

End Sub
 
Upvote 0
dmt32 said:
Hi,

The code you are sharing is your userforms initialize event which is used for code that prepares your userform for use before it is displayed.

However, your code will not do anything as you have made common error of renaming the event to that of your userform name.

You MUST not rename any event codes – it is ALWAYS UserForm_Initialize regardless of your userforms name.


See if this update to your code helps but if not, share the code that is assigned to your button

Place code in your userforms code page

Code: 
Private Sub UserForm_Initialize()
    Dim cntr        As Long
   
    Control21.Value = Application.UserName
    Control5 = Format(Now(), "dd/mmm/yyyy")
    Control22 = Format(Now(), "dd/mmm/yyyy")
   
    cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ"))
   
    Me.Control1.List = Cells(1, 36).Resize(cntr).Value
   
    Me.Control2.List = Array(6, 8, 10, 12, 14, 16, "XS", "M", "L", "XL")
   
    Me.Control15.List = Array("PAUL", "MIKE")
   
End Sub

I would also suggest that you give your controls meaningful names that follow a recognised Naming Convention

Hope helpful

Dave
Click to expand...
Hello Dave, I greatly appreciate Rob and You are helping me with this issue.
I'm keeping the name of the event as you advised, but still every time that I press the "Clear" button it will add up the same options
to the combo boxes as many times as I click on the button.
Regarding renaming the to my controls, I did it this way as I Use "Controls" as a variable in Previous/Next and search subroutines, so
I don't know how it would affect and how to change them. Again I'm new to VBA and I would need for guidance on this.

Thank you!
 
Upvote 0
Hi
It would seem that you are still using your UserForm1_Initialize code?

Try deleting the line

VBA Code: 
Call UserForm1_Initialize

and using my suggested code (do not rename it) & see if resolves your problem.

If you are still having issues, then post all your user forms code or better, place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave
 
Upvote 0
VBA Code: 
Dim lrow As Long
Dim activeRow As Long

Private Sub cmdExit_Click()
Dim iExit As VbMsgBoxResult

iExit = MsgBox("Do you want to exit the Application?", vbQuestion + vbYesNo, "Data Entry System")

If iExit = vbYes Then
Unload Me

End If

End Sub

Private Sub cmdNext_Click()

Dim FindRow
Dim cRow As String

On Error Resume Next

cRow = Me.control0.Value

Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues).Offset(1, 0)

If FindRow.Value = "" Then Exit Sub

Me.txtSearch = FindRow.Value

cNum = 23

For x = 0 To cNum
Me.Controls("Control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next

On Error GoTo 0

TextBox14.Value = activeRow

End Sub

Private Sub cmdPrevious_Click()
Dim FindRow
Dim cRow As String

On Error Resume Next

cRow = Me.control0.Value

Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues).Offset(-1, 0)

If FindRow.Value = Sheet3.Range("A1").Value Then Exit Sub

Me.txtSearch = FindRow.Value

cNum = 23

For x = 0 To cNum
Me.Controls("Control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next

On Error GoTo 0

TextBox14.Value = activeRow

End Sub

Private Sub cmdPrint_Click()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
'loop through all sheets in workbook
    LastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row
    'get the last row with data on Column N
    ws.PageSetup.PrintArea = "A1:N" & LastRow + 1
    'set print area from A1 to last row on column N
Next ws
Application.Dialogs(xlDialogPrinterSetup).Show
ThisWorkbook.Sheets("Inventory").PrintOut copies:=1

End Sub

Private Sub cmdSearch_Click()

Dim FindRow
Dim cRow As String

On Error GoTo errHandler

cRow = Me.txtSearch.Value
Set FindRow = Sheet3.Range("A:A").Find(What:=cRow, LookIn:=xlValues)

For x = 0 To 23
Me.Controls("control" & x).Value = FindRow
Set FindRow = FindRow.Offset(0, 1)
Next

On Error GoTo 0

Exit Sub

errHandler:
MsgBox " Error!.Reference Does not Exist!" & vbCrLf & Err.Description

End Sub

Private Sub cmdSave_Click()

Call UnProtectSheet

lrow = Sheets("Inventory").Range("A" & Rows.Count).End(xlUp).Row + 1

    If MsgBox("Do you wnat to Add a new Record?", vbYesNo + vbQuestion, "Question") = vbNo Then
    Exit Sub
    End If
 
 
    Sheets("Inventory").Cells(lrow, "A").Value = Me.control0.Value
    Sheets("Inventory").Cells(lrow, "B").Value = Me.control1.Value
    Sheets("Inventory").Cells(lrow, "C").Value = Me.control2.Value
    Sheets("Inventory").Cells(lrow, "D").Value = Me.control3.Value
    Sheets("Inventory").Cells(lrow, "E").Value = Me.control4.Value
    Sheets("Inventory").Cells(lrow, "F").Value = Me.control5.Value
    Sheets("Inventory").Cells(lrow, "G").Value = Me.control6.Value
    Sheets("Inventory").Cells(lrow, "H").Value = Me.control7.Value
    Sheets("Inventory").Cells(lrow, "J").Value = Me.control9.Value
    Sheets("Inventory").Cells(lrow, "M").Value = Me.control12.Value
    Sheets("Inventory").Cells(lrow, "N").Value = Me.control13.Value
    Sheets("Inventory").Cells(lrow, "P").Value = Me.control15.Value
 
   
    MsgBox "Record Added!"
  
    Call ProtectSheet
  
    Call Reset
  
  End Sub

Function Reset()

txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
'Me.control5 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
'Me.control21 = ""
'Me.control22 = ""

End Function

Private Sub cmdClear_Click()

txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
'Me.control5 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
'Me.control21 = ""
'Me.control22 = ""


MsgBox "Add New record"

End Sub

Private Sub UserForm_Initialize()
Dim cntr As Integer

control21.Value = Application.UserName            'Declaring User name For User Form
control5 = Format(Now(), "dd/mmm/yyyy")          'Declaring the Date for User Form
control22 = Format(Now(), "dd/mmm/yyyy")

cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ"))    ' Method 1 for Combo Box creating range List in Column AJ of Sheet 3
For i = 1 To cntr
Me.control1.AddItem Cells(i, 36)

Next i

Me.control2.AddItem "6"                              'Method 2 for Combo Box
Me.control2.AddItem "8"
Me.control2.AddItem "10"
Me.control2.AddItem "12"
Me.control2.AddItem "14"
Me.control2.AddItem "16"
Me.control2.AddItem "XS"
Me.control2.AddItem "M"
Me.control2.AddItem "L"
Me.control2.AddItem "XL"

With Me.control15                           'Method 3 for Combo Box
    .AddItem "MIKE"
    .AddItem "PAUL"
End With

End Sub
Private Sub cmdUpdate_Click()

Call UnProtectSheet

Dim REFERENCE_NUMBER As String
control22.Text = Date
REFERENCE_NUMBER = Trim(control0.Text)

LastRow = Worksheets("Inventory").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow

If Worksheets("Inventory").Cells(i, 1).Value = REFERENCE_NUMBER Then

If MsgBox("Do you Want to Update Record ?", vbYesNo + vbQuestion, "Question") = vbNo Then
Exit Sub
End If

Worksheets("Inventory").Cells(i, 23) = Format(Now(), "dd/mmm/yyyy")

Worksheets("Inventory").Cells(i, 1).Value = control0.Text
Worksheets("Inventory").Cells(i, 2).Value = control1.Text
Worksheets("Inventory").Cells(i, 3).Value = control2.Text
Worksheets("Inventory").Cells(i, 4).Value = control3.Text
Worksheets("Inventory").Cells(i, 5).Value = control4.Text
Worksheets("Inventory").Cells(i, 6).Value = control5.Text
Worksheets("Inventory").Cells(i, 7).Value = control6.Text
Worksheets("Inventory").Cells(i, 8).Value = control7.Text
Worksheets("Inventory").Cells(i, 10).Value = control9.Text
Worksheets("Inventory").Cells(i, 13).Value = control12.Text
Worksheets("Inventory").Cells(i, 14).Value = control13.Text
Worksheets("Inventory").Cells(i, 16).Value = control15.Text


MsgBox "Record Updated!"

End If
Next

Call ProtectSheet

Call Reset2

End Sub

Function Reset2()

txtSearch = ""
Me.control0 = ""
Me.control1 = ""
Me.control2 = ""
Me.control3 = ""
Me.control4 = ""
'Me.control5 = ""
Me.control6 = ""
Me.control7 = ""
Me.control8 = ""
Me.control9 = ""
Me.control10 = ""
Me.control11 = ""
Me.control12 = ""
Me.control13 = ""
Me.control14 = ""
Me.control15 = ""
Me.control16 = ""
Me.control17 = ""
Me.control19 = ""
Me.control20 = ""
'Me.control21 = ""
'Me.control22 = ""


End Function


Private Sub control7_Change()
    control7.Value = Format(control7.Value, "$#,##0")
End Sub

Private Sub control6_Change()
    control6.Value = Format(control6.Value, "$#,##0")
End Sub

Private Sub control8_Change()
    control8.Value = Format(control8.Value, "$#,##0")
End Sub


Private Sub txtSearch_Change()
'Show Upper Case
Me.txtSearch = UCase(Me.txtSearch)

End Sub

Private Sub control0_Change()
'Show Upper Case
Me.control0 = UCase(Me.control0)

End Sub
Private Sub control2_Change()
'Show Upper Case
Me.control2 = UCase(Me.control2)

End Sub
Private Sub control3_Change()
'Show Upper Case
Me.control3 = UCase(Me.control3)

End Sub

Private Sub control5_Change()
'Show Upper Case
Me.control5 = UCase(Me.control5)

End Sub
Private Sub txtCustomer_Change()
'Show Upper Case
Me.txtCustomer = UCase(Me.txtCustomer)

End Sub

Private Sub control4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Not (KeyAscii >= 48 And KeyAscii <= 57) Then    ' Prevent from entering letters in box
    KeyAscii = 0
End If

End Sub
Private Sub control6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Not (KeyAscii >= 48 And KeyAscii <= 57) Then    ' Prevent from entering letters in box
    KeyAscii = 0
End If

End Sub
Private Sub control7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Not (KeyAscii >= 48 And KeyAscii <= 57) Then    ' Prevent from entering letters in box
    KeyAscii = 0
End If

End Sub

Private Sub txtPrecioCad_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Not (KeyAscii >= 48 And KeyAscii <= 57) Then    ' Prevent from entering letters in box
    KeyAscii = 0
End If

End Sub
Private Sub txtSippingCad_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Not (KeyAscii >= 48 And KeyAscii <= 57) Then    ' Prevent from entering letters in box
    KeyAscii = 0
End If

End Sub

Hello Dave,
I did what you advised, and still it does not seem to work. Please see the complete code of the User Form as requested.

Thank you!
 
Last edited by a moderator:
Upvote 0
Clear the controls first:

VBA Code: 
me.control1.clear
For i = 1 To cntr
Me.control1.AddItem Cells(i, 36)

Next i
me.control2.clear
Me.control2.AddItem "6" 'Method 2 for Combo Box
Me.control2.AddItem "8"
Me.control2.AddItem "10"
Me.control2.AddItem "12"
Me.control2.AddItem "14"
Me.control2.AddItem "16"
Me.control2.AddItem "XS"
Me.control2.AddItem "M"
Me.control2.AddItem "L"
Me.control2.AddItem "XL"

With Me.control15 'Method 3 for Combo Box
.clear
.AddItem "PAUL"
.AddItem "MIKE"
End With
 
Upvote 0
Yeft said:
Hello Dave, I did what you advised, and still it does not seem to work.
Click to expand...
Not quite, I provided this update to the UserForm_Initialize event for you to try

VBA Code: 
Private Sub UserForm_Initialize()
    Dim cntr        As Long
    
    Control21.Value = Application.UserName
    Control5 = Format(Now(), "dd/mmm/yyyy")
    Control22 = Format(Now(), "dd/mmm/yyyy")
    
    cntr = Application.WorksheetFunction.CountA(Range("AJ:AJ"))
    
    Me.Control1.List = Cells(1, 36).Resize(cntr).Value
    
    Me.Control2.List = Array(6, 8, 10, 12, 14, 16, "XS", "M", "L", "XL")
    
    Me.Control15.List = Array("PAUL", "MIKE")
    
End Sub

Dave
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question
Reset user form combo box to top record
Replies
5
Views
594
James006
James006
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
887
RoryA
RoryA
R
  • Question
Making sure that all options in User Form are selected
Replies
1
Views
489
Rainmanne
R
brendalpzm
  • Question
Clear combo box when changing option button
Replies
1
Views
768
sijpie
sijpie
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
504
AndyEd
A

Forum statistics

Threads
1,216,117
Messages
6,128,935
Members
449,480
Latest member
yesitisasport

Share this page

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