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() Wit...
excel
Macros and VBA
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.
    • Mr_Buttons's avatar
      Mr_Buttons
      Copper Contributor
      Feb 21, 2020

      Hey JKPieterse

      I edited and cleaned up my code to make the submit button work. I added this code and it works now. Thanks!

      Private Sub btnsubmit_Click()
'Copy input values to sheet.
Dim irow As Long

'Determine empty row
' emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
With Sheet1
irow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(irow, 1).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
.Cells(irow, 2).Value = Me.txtbatch.Value
.Cells(irow, 3).Value = Me.cmbchart.Value
.Cells(irow, 4).Value = Me.cmbtrigger.Value
.Cells(irow, 5).Value = Me.txtfail.Value
.Cells(irow, 6).Value = Me.txtdisreview.Value
.Cells(irow, 7).Value = Me.txtdateac.Value
.Cells(irow, 8).Value = Me.txtempid.Value
End With
'Clear input controls.
Me.cmbdate.Value = ""
Me.cmbmonth.Value = ""
Me.cmbyear.Value = ""
Me.txtbatch.Value = ""
Me.cmbchart.Value = ""
Me.cmbtrigger.Value = ""
Me.txtfail.Value = ""
Me.txtdisreview.Value = ""
Me.txtdateac.Value = ""
Me.txtempid.Value = ""
End Sub


       

Resources