Userform runs ok when started in VBA Editor, but not when started from macro in workbook.

vba , excel , userform Yonkers, United States
  • 8 years ago

    Hi. I need help to figure out how to fix the problem I am having with a userform that I created to enter names and adddress into an Excel worksheet. Part of the proccess involves not allowing a duplicate address to be entered into the worksheet. When I run the code in the Editor the whole process works fine, but when I run a macro in the workbook for the form everything works fine except the process allowing duplicates.... Can anyone tell me how to fix this? See code below:

    thanks. Doug

    Private Sub cmdAdd_Click() Dim RowCount As Long Dim ctl As Control

    'Check User Input If Me.txtDay.Value = "" Then MsgBox "Please Enter a Day", vbExclamation, "ClientList" Me.txtDay.SetFocus Exit Sub End If If Me.txtMonth.Value = "" Then MsgBox "Please Enter a Month", vbExclamation, "ClientList" Me.txtMonth.SetFocus Exit Sub End If If Me.txtYear.Value = "" Then MsgBox "Please Enter a Year", vbExclamation, "ClientList" Me.txtYear.SetFocus Exit Sub End If If Me.txtTitle.Value = "" Then MsgBox "Please Enter a Title", vbExclamation, "ClientList" Me.txtTitle.SetFocus Exit Sub End If If Me.txtFirst.Value = "" Then MsgBox "Please Enter a First Name", vbExclamation, "ClientList" Me.txtFirst.SetFocus Exit Sub End If If Me.txtLast.Value = "" Then MsgBox "Please Enter a Last Name", vbExclamation, "ClientList" Me.txtLast.SetFocus Exit Sub End If If Me.txtAddress.Value = "" Then MsgBox "Please Enter an Address", vbExclamation, "ClientList" Me.txtAddress.SetFocus Exit Sub End If If Me.txtCity.Value = "" Then MsgBox "Please Enter a City", vbExclamation, "ClientList" Me.txtCity.SetFocus Exit Sub End If If Me.txtState.Value = "" Then MsgBox "Please Enter a State", vbExclamation, "ClientList" Me.txtState.SetFocus Exit Sub End If

    'Check for Duplicates If Application.CountIf(Range("H1:H500"), txtAddress.Text) > 0 Then MsgBox ("This address already exists!") For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = "" ElseIf TypeName(ctl) = "CheckBox" Then ctl.Value = False End If Next ctl Exit Sub End If

    'Write Data to Worksheet RowCount = Worksheets("ClientList").Range("A1").CurrentRegion.Rows.Count With Worksheets("ClientList").Range("A1") .Offset(RowCount, 0).Value = Me.txtDay.Value .Offset(RowCount, 1).Value = Me.txtMonth.Value .Offset(RowCount, 2).Value = Me.txtYear.Value .Offset(RowCount, 3).Value = Me.txtTitle.Value .Offset(RowCount, 4).Value = Me.txtFirst.Value .Offset(RowCount, 5).Value = Me.txtMiddle.Value .Offset(RowCount, 6).Value = Me.txtLast.Value .Offset(RowCount, 7).Value = Me.txtAddress.Value .Offset(RowCount, 8).Value = Me.txtCity.Value .Offset(RowCount, 9).Value = Me.txtState.Value End With

    'Clear Cells For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = "" ElseIf TypeName(ctl) = "CheckBox" Then ctl.Value = False End If Next ctl

    End Sub

    Private Sub cmdClose_Click() Unload Me End Sub

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous