RE: VBA Code will run after "optOthers" Option box is selected.

[ShangX89]

RE: VBA Code will run after "optOthers" Option box is selected.

I have created a Userform to help ease the flow for data entry for a friend's business.

The userform consists of text boxes, option boxes and checkboxes (used for a checklist).

The textboxes are used to input CustomerName, TaxInvoiceNumber and also Date.

I have created an option whereby the Date can be selected using the option boxes "Today" and "Others".

When Today is selected, the textbox on the userform will be disabled with today's date inserted.

The vba code works fine with after pressing add.

However, this does not work when I choose "Others" and Input the date into the Textbox. After clicking the "Add" button. Nothing seems to run at all.

The code is written as below:

Private Sub cmdAdd_Click()
Dim j As Integer
Dim k As Integer
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim ChkNo As Integer
Dim Complete As Integer
''''''''''Identifying Empty Boxes'''''''''''
If Me.txtCustomerName.Value = "" Then
 MsgBox "Please enter Customer Name.", vbExclamation, "Sales Checklist"
 Me.txtCustomerName.SetFocus
 Exit Sub
 End If
If Me.txtInvoiceNumber.Value = "" Then
 MsgBox "Please enter Tax Invoice Number.", vbExclamation, "Sales Checklist"
 Me.txtInvoiceNumber.SetFocus
 Exit Sub
 End If
If Me.optToday.Value = False And Me.optOthers.Value = False Then
MsgBox "Please make Date selection.", vbExclamation, "Sales Checklist"
Exit Sub
End If
If Me.optOthers.Value = True Then
If Not IsDate(Me.txtDate.Value) Then
 MsgBox "Insert Date in DD/MM/YYYY format", vbExclamation, "Sales Checklist"
 Me.txtDate.SetFocus
 Exit Sub
 End If
 Exit Sub
 End If
 
If Me.cboSalesType.Value = "" Then
MsgBox "Please select Sales Type.", vbExclamation, "Sales Checklist"
Me.cboSalesType.SetFocus
Exit Sub
End If
'''''''''''Insert New Row'''''''''''
Set Tbl = Range("tblRegistryLog").ListObject
Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
'''''''''''Insert Values'''''''''''''
With ws
NewRow.Range.Cells(1, 1).Value = Me.txtCustomerName.Value
NewRow.Range.Cells(1, 2).Value = Me.txtInvoiceNumber.Value
''''''' Insert Date ''''''
'If Me.optToday.Value = True Then
NewRow.Range.Cells(1, 3).Value = DateValue(Me.txtDate.Value)
'Else
'Me.txtDate.Value = Date
'Me.txtDate.Value = Format(Date, "DD/MM/YYYY")
'NewRow.Range.Cells(1, 3).Value = DateValue(Me.txtDate.Value)
'End If
NewRow.Range.Cells(1, 4).Value = Me.cboSalesType.Value
'''''''''''''Check list'''''''''''''''
ChkNo = 9
For j = 1 To ChkNo
k = j + 4
If frmRoutineTask.Controls("chk" & j).Value = True Then
NewRow.Range.Cells(1, k).Value = 1
Complete = NewRow.Range.Cells(1, k).Value + Complete
Else
NewRow.Range.Cells(1, k).Value = 0
End If
Next
NewRow.Range.Cells(1, 14).Value = Complete / ChkNo
NewRow.Range.Cells(1, 14).NumberFormat = "0.0%"
If NewRow.Range.Cells(1, 14).Value < 100 Then
NewRow.Range.Cells(1, 15).Value = "Pending"
Else
NewRow.Range.Cells(1, 15).Value = "Completed"
End If
NewRow.Range.Cells(1, 16).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
NewRow.Range.Cells(1, 17).Value = Environ("Username")
NewRow.Range.Cells(1, 18).Value = MonthName(Month(Me.txtDate.Value))
NewRow.Range.Cells(1, 19).Value = Year(Me.txtDate.Value)
End With
'''''''''''''Select Row'''''''''''''''
cmdAdd.Enabled = -False
NewRow.Range.Select
End Sub
 
 
 
Private Sub optOthers_Click()
Me.txtDate.Value = ""
Me.txtDate.Enabled = True
End Sub

Private Sub optToday_Click()
Me.txtDate.Value = Date
Me.txtDate.Value = Format(Date, "DD/MM/YYYY")
Me.txtDate.Enabled = False
End Sub

 

[sykes]

Re: VBA Code will run after "optOthers" Option box is selected.

I think it's this bit:

If Me.optOthers.Value = True Then
If Not IsDate(Me.txtDate.Value) Then
 MsgBox "Insert Date in DD/MM/YYYY format", vbExclamation, "Sales Checklist"
 Me.txtDate.SetFocus
 Exit Sub
 End If
 Exit Sub
 End If

As long as "optOthers" is selected, and the value in "txtDate" is correct (is a date), then I think your code's skipping to the last "Exit Sub" of that part of your routine, and, of course is exitting the routine.

Try removing it:

If Me.optOthers.Value = True Then
If Not IsDate(Me.txtDate.Value) Then
 MsgBox "Insert Date in DD/MM/YYYY format", vbExclamation, "Sales Checklist"
 Me.txtDate.SetFocus
 Exit Sub
 End If
 
 End If