heroofgoodwi
11-24-2017, 07:48 AM
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
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