Forum Discussion

Mr_Buttons's avatar
Mr_Buttons
Copper Contributor
Feb 19, 2020

Userform - Submit button not working

Hello everyone.

I made a UserForm using VBA for the first time. I'm unable to get the submit button working. Am I missing any steps in my code below?

 

 

Private Sub qualform_Initialize()

With Me
 .StartUpPosition = 1
 .Width = Application.Width * 10
 .Height = Application.Height * 10
 .Left = Application.Left + (Application.Width * 10) \ 1
 .Top = Application.Top + (Application.Height * 10) \ 1
 End With
 
cmbdate.Clear
cmbmonth.Clear
cmbyear.Clear
   
'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "2020"
      .AddItem "2021"
      .AddItem "2022"
      .AddItem "2023"
      .AddItem "2024"
      .AddItem "2025"
      .AddItem "2026"
      .AddItem "2027"
      .AddItem "2028"
      .AddItem "2029"
      .AddItem "2030"
      .AddItem "2031"
      .AddItem "2032"
      .AddItem "2033"
      .AddItem "2034"
      .AddItem "2035"
      .AddItem "2036"
      .AddItem "2037"
      .AddItem "2038"
      .AddItem "2039"
      .AddItem "2040"
   End With
   
End Sub

Private Sub btncancel_Click()
   Unload Me
End Sub

Private Sub btnsubmit_Click()

 'Copy input values to sheet.
    Dim lRow As Long
    
    'Make Sheet1 Active
    Sheet1.Activate
    
    'Determine empty row
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    With ws
        .Cells(lRow, 1).Value = Me.empid.Value
        .Cells(lRow, 2).Value = Me.disreview.Value
        .Cells(lRow, 3).Value = Me.daterec.Value
        .Cells(lRow, 4).Value = Me.batch.Value
        .Cells(IRow, 5).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
    End With
    'Clear input controls.
    Me.empid.Value = ""
    Me.disreview.Value = ""
    Me.daterec.Value = ""
    Me.batch.Value = ""
    Me.dateac.Value = ""
    
End Sub

 

 

 

Thanks for the help everyone!

excel
Macros and VBA

4 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Feb 19, 2020
    Advice 1: turn on variable declaration by adding the statement
    Option Explicit
    at the top of the module. This will tell you that this line:
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    contains an undeclared variable. CHange the line to:
    lRow = WorksheetFunction.CountA(Range("A:A")) + 1
    • Mr_Buttons's avatar
      Mr_Buttons
      Copper Contributor
      Feb 19, 2020

      Hi Jan,

      Thanks for the reply. I made the changes as you suggested. However, I'm not sure why I'm getting the method not found error as per below. Is it because of the "value" attribute?

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Feb 19, 2020
        I'm not sure, if EmpID is a textbox on the userform it should work as shown.

Resources