User Form Assistance

[heroofgoodwi]

Hey Guys,

So I have been having a doozie of a week and appear to have hit a brick wall while creating a userform which can be filled out and then will allow the user to submit data directly into the worksheet. The only problem I appear to be having is that the Submit Button inserted in the form does not appear to work at all and I for the life of me cannot figure out why. Any help would be greatly appreciated.

Code showed below:

        'Variable Decleration
        Dim BlnVal As Boolean
Private Sub UserForm1_Initialize()
    'Variable Declaration
    Dim IdVal As Integer
        
        'Finding last row in the data sheet
        IdVal = fn_LastRow(Sheets("Data"))
        'Update next available id on the userform
        frmData.txtId = IdVal
End Sub
Sub CmdAdd_Click()
On Error GoTo ErrOccurred
'Boolean Value
BlnVal = 0
'Data Validation
Call Data_Validation
'Check validation fields are completed or not
If BlnVal = 0 Then Exit Sub
'Turn off Screen Updating
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
'Variable decleration
Dim TextBoxName     'name of user
Dim TextBoxDate     'Date
Dim TextBoxIssue    'Issue
Dim TextBoxIdea     'Idea
Dim ComboBoxIdea    'Idea type
Dim ComboBoxLead1  'Result
Dim ComboBoxLead2  'Result
Dim ComboBoxLead3   'Result
Dim ComboBoxLead4   'Result
Dim TextBoxEmail    'User contact details
Dim CommandButtonSubmit 'submit button
Dim iCnt As Integer
    'Find next available row to update data in the data worksheet
    iCnt = fn_LastRow(Sheets("Data")) + 1
    
        'Update user form data to the worksheet
        With Sheets("Data")
        .Cells(iCnt, 1) = iCnt - 1
        .Cells(iCnt, 2) = TextBoxName
        .Cells(iCnt, 3) = TextBoxDate
        .Cells(iCnt, 4) = TextBoxIssue
        .Cells(iCnt, 5) = TextBoxIdea
        .Cells(iCnt, 6) = ComboBoxIdea
        .Cells(iCnt, 7) = ComboBoxLead1
        .Cells(iCnt, 8) = ComboBoxLead2
        .Cells(iCnt, 9) = ComboBoxLead3
        .Cells(iCnt, 10) = ComboBoxLead4
        .Cells(iCnt, 11) = TextBoxEmail
'Formatting Data
.Columns("A:J").Columns.AutoFit
.Range("A1:J1").Font.Bold = True
.Range("A1:J1").LineStyle = xlDash
        End With
        
    'Display next available ID number on the userform
    'Variable decleration
    Dim IdVal As Integer
    'Finding last row in the data sheet
    IdVal = fn_LastRow(Sheets("Data"))
    'Update next available id on the userform
    frmData.txtld = IdVal
ErrOccurred:
    'TurnOn Screen Updating
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
'Function to find the last row of specified sheet
Function fn_LastRow(ByVal Sht As Worksheet)
Dim lastRow As Long
lastRow = Sht.Cells.SpecialCells(xcLastCell).Row
lRow = She.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(She.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
fn_LastRow = lRow
End Function
'Check all the data has entered are not on the user form
Sub Data_Validation()
If TextBoxName = "" Then
MsgBox "Enter Name!", vbInformation, "Name"
Exit Sub
ElseIf TextBoxDate = "" Then
MsgBox "Enter Date", vbInformation, "Date"
Exit Sub
ElseIf TextBoxEmail = "" Then vbInformation , "Contact Details"
MsgBox "Please enter an email address so we can contact you on the status of your innovation idea"
Exit Sub
Else
BlnVal = 1
End If
End Sub
'Exit from the user form
Private Sub CmbCancel_Click()
Unload Me
End Sub
 
'Clearing data field of user form
Private Sub cmdClear_Click()
Application.ScreenUpdating = False
TextBoxName = ""
TextBoxDate = ""
TextBox Issue = ""
TextBoxIdea = ""
ComboBoxIdea = ""
ComboBoxLead1 = ""
ComboBoxLead2 = ""
ComboBoxLead3 = ""
ComboBoxLead4 = ""
TextBoxEmail = ""
Application.ScreenUpdating = True
End Sub
 
'Final Code
Private Sub UserForm_Initialize()
'combo box "is this an idea for"
ComboBoxIdea.AddItem "Innovation"
ComboBoxIdea.AddItem "Process/Lean Improvement"
ComboBoxIdea.AddItem "Value Management/ Efficiency"
'combo box "would this lead to 1"
ComboBoxLead1.AddItem ""
ComboBoxLead1.AddItem "Reduction in cost"
ComboBoxLead1.AddItem "Reduction in time"
ComboBoxLead1.AddItem "Higher Quality/ Added Value"
ComboBoxLead1.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 2"
ComboBoxLead2.AddItem ""
ComboBoxLead2.AddItem "Reduction in cost"
ComboBoxLead2.AddItem "Reduction in time"
ComboBoxLead2.AddItem "Higher Quality/ Added Value"
ComboBoxLead2.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 3"
ComboBoxLead3.AddItem ""
ComboBoxLead3.AddItem "Reduction in cost"
ComboBoxLead3.AddItem "Reduction in time"
ComboBoxLead3.AddItem "Higher Quality/ Added Value"
ComboBoxLead3.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 4"
ComboBoxLead4.AddItem ""
ComboBoxLead4.AddItem "Reduction in cost"
ComboBoxLead4.AddItem "Reduction in time"
ComboBoxLead4.AddItem "Higher Quality/ Added Value"
ComboBoxLead4.AddItem "Delivering Scheme Objectives"
End Sub

[Paul_Hossler]

Not having the workbook to play with, if you comment out the On Error Goto ErrOccured, you might get more information about the issue

Also add Option Explicit at the beginning of the module

    On Error GoTo ErrOccurred 
     'Boolean Value
    BlnVal = 0 
    'Data Validation
    Call Data_Validation 
     'Check validation fields are completed or not
    If BlnVal = 0 Then Exit Sub

Also, since you Dim-ed BlnVal as Boolean, I'd just use

    BlnVal = False
    'Data Validation
    Call Data_Validation 
    'Check validation fields are completed or not
    If Not BlnVal Then Exit Sub

Also #3 - My (very) personal style would be to use variable names that are a little more 'self documenting', e.g. Dim 'Validation_OK'

[heroofgoodwi]

Awesome stuff cheers for the help.

Any Idea on how to make the Submit button work?
To clarify further the button isn't throwing up an error. Instead it literally does nothing.

Dim CommandButtonSubmit 'submit button

[Paul_Hossler]

Wild guess / possibility

The event handler code:

Sub CmdAdd_Click()


is in the UserForm1 code module I assume, and the .Name (not the .Caption) for your [Submit] is 'CmdAdd'

[heroofgoodwi]

Paul you are a genius. Thank you.

Code now looks like this (Shown below) and the submit button works.

However a new problem has arose, the data being filled is only the ID tag, leaving the remaining fields unfilled even if there is content in the user form it is not being added to the sheet.

Option Explicit
        'Variable Decleration
        Dim BlnVal As Boolean
Private Sub UserForm1_Initialize()
    'Variable Declaration
    Dim IdVal As Integer
        
        'Finding last row in the data sheet
        IdVal = fn_LastRow(Sheets("Data"))
        'Update next available id on the userform
        frmData.txtId = IdVal
End Sub
Sub CmdAdd_Click()
On Error GoTo ErrOccurred
'Boolean Value
BlnVal = False
'Data Validation
Call Data_Validation
'Check validation fields are completed or not
If Not BlnVal Then Exit Sub
'Turn off Screen Updating
'Sheets("Data").Activate
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
'Variable decleration
Dim TextBoxName As TextBox      'name of user
Dim TextBoxDate As TextBox      'Date
Dim TextBoxIssue As TextBox     'Issue
Dim TextBoxIdea As TextBox      'Idea
Dim ComboBoxIdea  As ComboBox   'Idea type
Dim ComboBoxLead1 As ComboBox 'Result
Dim ComboBoxLead2 As ComboBox 'Result
Dim ComboBoxLead3 As ComboBox 'Result
Dim ComboBoxLead4 As ComboBox 'Result
Dim TextBoxEmail As TextBox   'User contact details
Dim DataSheet As Worksheet
Dim CommandButtonSubmit As CommandButton    'submit button
Dim iCnt As Integer

    'Find next available row to update data in the data worksheet
    iCnt = fn_LastRow(Sheets("Data")) + 1
    
        'Update user form data to the worksheet
        With Sheets("Data")
        'Sheets("Data").Activate
        .Cells(iCnt, 1).Value = iCnt - 1
        .Cells(iCnt, 2).Value = TextBoxName
        .Cells(iCnt, 3).Value = TextBoxDate
        .Cells(iCnt, 4).Value = TextBoxIssue
        .Cells(iCnt, 5).Value = TextBoxIdea
        .Cells(iCnt, 6).Value = ComboBoxIdea
        .Cells(iCnt, 7).Value = ComboBoxLead1
        .Cells(iCnt, 8).Value = ComboBoxLead2
        .Cells(iCnt, 9).Value = ComboBoxLead3
        .Cells(iCnt, 10).Value = ComboBoxLead4
        .Cells(iCnt, 11).Value = TextBoxEmail
'Formatting Data
Sheets("Data").Columns("A:J").Columns.AutoFit
Sheets("Data").Range("A1:J1").Font.Bold = True
Sheets("Data").Range("A1:J1").LineStyle = xlDash
        End With
        
    'Display next available ID number on the userform
    'Variable decleration
    Dim IdVal As Integer
    'Finding last row in the data sheet
    IdVal = fn_LastRow(Sheets("Data"))
    'Update next available id on the userform
    'frmData.txtld = IdVal
ErrOccurred:
    'TurnOn Screen Updating
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
'Function to find the last row of specified sheet
Function fn_LastRow(ByVal Sht As Worksheet)
Dim lastRow As Long
Dim lRow As Long
lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
lRow = Sht.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
fn_LastRow = lRow
End Function
'Check all the data has entered are not on the user form
Sub Data_Validation()
If TextBoxName = "" Then
MsgBox "Enter Name!", vbInformation, "Name"
Exit Sub
ElseIf TextBoxDate = "" Then
MsgBox "Enter Date", vbInformation, "Date"
Exit Sub
'ElseIf TextBoxEmail = "" Then vbInformation , "Contact Details"
'MsgBox "Please enter an email address so we can contact you on the status of your innovation idea"
'Exit Sub
Else
BlnVal = True
End If
End Sub
'Exit from the user form
Private Sub CmbCancel_Click()
Unload Me
End Sub
 
'Clearing data field of user form
Private Sub cmdClear_Click()
Application.ScreenUpdating = False
TextBoxName = ""
TextBoxDate = ""
TextBox Issue = ""
TextBoxIdea = ""
ComboBoxIdea = ""
ComboBoxLead1 = ""
ComboBoxLead2 = ""
ComboBoxLead3 = ""
ComboBoxLead4 = ""
TextBoxEmail = ""
Application.ScreenUpdating = True
End Sub
 
 
 
Private Sub CommandButtonSubmit_Click()
Call CmdAdd_Click
End Sub
'Final Code
Private Sub UserForm_Initialize()
'combo box "is this an idea for"
ComboBoxIdea.AddItem "Innovation"
ComboBoxIdea.AddItem "Process/Lean Improvement"
ComboBoxIdea.AddItem "Value Management/ Efficiency"
'combo box "would this lead to 1"
ComboBoxLead1.AddItem ""
ComboBoxLead1.AddItem "Reduction in cost"
ComboBoxLead1.AddItem "Reduction in time"
ComboBoxLead1.AddItem "Higher Quality/ Added Value"
ComboBoxLead1.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 2"
ComboBoxLead2.AddItem ""
ComboBoxLead2.AddItem "Reduction in cost"
ComboBoxLead2.AddItem "Reduction in time"
ComboBoxLead2.AddItem "Higher Quality/ Added Value"
ComboBoxLead2.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 3"
ComboBoxLead3.AddItem ""
ComboBoxLead3.AddItem "Reduction in cost"
ComboBoxLead3.AddItem "Reduction in time"
ComboBoxLead3.AddItem "Higher Quality/ Added Value"
ComboBoxLead3.AddItem "Delivering Scheme Objectives"
'combo box "would this lead to 4"
ComboBoxLead4.AddItem ""
ComboBoxLead4.AddItem "Reduction in cost"
ComboBoxLead4.AddItem "Reduction in time"
ComboBoxLead4.AddItem "Higher Quality/ Added Value"
ComboBoxLead4.AddItem "Delivering Scheme Objectives"
End Sub

[SamT]

Dim TextBoxName As TextBox 'name of user
    Dim TextBoxDate As TextBox 'Date
    Dim TextBoxIssue As TextBox 'Issue
    Dim TextBoxIdea As TextBox 'Idea
    Dim ComboBoxIdea  As ComboBox 'Idea type
    Dim ComboBoxLead1 As ComboBox 'Result
    Dim ComboBoxLead2 As ComboBox 'Result
    Dim ComboBoxLead3 As ComboBox 'Result
    Dim ComboBoxLead4 As ComboBox 'Result
    Dim TextBoxEmail As TextBox
Dim CommandButtonSubmit As CommandButton 'submit button

Big NoNo. They are already declared just by their presence on the Form.
If you insist on using Variables as Control "avatars," then you must explicitly Set each "Avatar" to the Control it represents. And they can't use the same name

    Dim TextBoxDate As TextBox 'Date
Set TextBoxDate = Me.Controls("txtTextBoxDate")

[Paul_Hossler]

I don't think you want to Dim these.

The control on the UserForm (probably with the same name) is the one you want, but these are higher in the scope than the ones on the UserForm

Since you never assign anything to these, they essentially remain 'blank'

 Dim TextBoxName As TextBox 'name of user
    Dim TextBoxDate As TextBox 'Date
    Dim TextBoxIssue As TextBox 'Issue
    Dim TextBoxIdea As TextBox 'Idea
    Dim ComboBoxIdea  As ComboBox 'Idea type
    Dim ComboBoxLead1 As ComboBox 'Result
    Dim ComboBoxLead2 As ComboBox 'Result
    Dim ComboBoxLead3 As ComboBox 'Result
    Dim ComboBoxLead4 As ComboBox 'Result
    Dim TextBoxEmail As TextBox 'User contact details
    Dim DataSheet As Worksheet <<<<<<<<<<<<<<<<<<<<<<<<< leave this probably
    Dim CommandButtonSubmit As CommandButton 'submit button

The fully expanded line of code would be something like

 .Cells(iCnt, 2).Value = UserForm1.TextBoxName.Value

or

 .Cells(iCnt, 2).Value = me.TextBoxName.Value

Post WB is you can't get it

[SamT]

Paul, I think you mean that the inner sub Variables are tighter in scope than the UserForm Controls.

Example Standard module code

Dim X as Object
Dim y
Set X = UserForm1.SomeControlName
'or at least
Set X = UserForm1.Cotrols("SomeControlName")

With X
y = .Value
.Left = 1
.Top = 1
End with

But you cannot

Set X = UserForm1.CmdAdd.TextBoxName

Where TextBoxName is some Variable inside Sub CmdAdd_Click

[Paul_Hossler]

Paul, I think you mean that the inner sub Variables are tighter in scope than the UserForm Controls.

I was a little sloppy -- I mean they were higher in priority

Thanks for clarifying