ComboBox search for variable...
 

I am doing VBA for a spreadsheet at work. The idea behind this
spreadsheet is to have a UserForm that asks for a certain Month
(cmbMonth) & Day (cmbDate) and Dollar Amount (txt.EndCash). Then,
using the Month, it goes to a certain sheet (named "January",
"February", etc.) and finds the cell with the specific date already in
there, then goes 3 cells to the right to input the dollar amount
specified. So far, I have succeeded in code that specifies the sheet,
but have drawn a blank on something that can assign a variable to
cmbDate and search for it on the sheet in A6:A37.

Here's my current code if that helps out any (most of it applies to
the OK, Cancel & Clear buttons, obviously:

-----------------------------------------
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox"
Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()

ScreenUpdating = False
Val1 = txtEndCash
TheSheet = cmbMonth.Value
Worksheets(TheSheet).Activate
ActiveSheet.Range("H3") = Val1
ScreenUpdating = True

Dim RowCount As Long
RowCount =
Worksheets("January").Range("D20").CurrentRegion.R ows.Count
With Worksheets("January").Range("D20")
.Offset(RowCount, 0).Value = Me.txtEndCash.Value
End With

If Me.cmbMonth.Value = "" Then
MsgBox "Please Enter A Month!", vbExclamation, "Date Error"
Me.cmbMonth.SetFocus
Exit Sub
End If

If Me.cmbDate.Value = "" Then
MsgBox "Please Enter A Date!", vbExclamation, "Date Error"
Me.cmbDate.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "0.00" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtEndCash.Value) Then
MsgBox "Hey, dummy! The END CASH Amount must be a NUMBER!",
vbExclamation, "Money Error"
Me.txtEndCash.SetFocus
Exit Sub
End If


End Sub



Private Sub UserForm_Click()

End Sub


--------

Thanks in advance for any help!

ComboBox search for variable...
 

Maybe you can fit this into your code:

Dim res as variant
dim myRng as range

with worksheets(TheSheet)
set myrng = .range("a6:a37")
'cmbdate.value is text.
'I'm guessing that the values in A6:A37 are really numbers
res = application.match(clng(cmbdate.value),myrng,0)
if iserror(res) then
msgbox "That date isn't on " & .name & "!"
else
myrng(res).offset(0,3).value = txtEndCash 'not txt.endcash???
end if
end with

Señor Rubia wrote:

I am doing VBA for a spreadsheet at work. The idea behind this
spreadsheet is to have a UserForm that asks for a certain Month
(cmbMonth) & Day (cmbDate) and Dollar Amount (txt.EndCash). Then,
using the Month, it goes to a certain sheet (named "January",
"February", etc.) and finds the cell with the specific date already in
there, then goes 3 cells to the right to input the dollar amount
specified. So far, I have succeeded in code that specifies the sheet,
but have drawn a blank on something that can assign a variable to
cmbDate and search for it on the sheet in A6:A37.

Here's my current code if that helps out any (most of it applies to
the OK, Cancel & Clear buttons, obviously:

-----------------------------------------
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox"
Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()

ScreenUpdating = False
Val1 = txtEndCash
TheSheet = cmbMonth.Value
Worksheets(TheSheet).Activate
ActiveSheet.Range("H3") = Val1
ScreenUpdating = True

Dim RowCount As Long
RowCount =
Worksheets("January").Range("D20").CurrentRegion.R ows.Count
With Worksheets("January").Range("D20")
.Offset(RowCount, 0).Value = Me.txtEndCash.Value
End With

If Me.cmbMonth.Value = "" Then
MsgBox "Please Enter A Month!", vbExclamation, "Date Error"
Me.cmbMonth.SetFocus
Exit Sub
End If

If Me.cmbDate.Value = "" Then
MsgBox "Please Enter A Date!", vbExclamation, "Date Error"
Me.cmbDate.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Me.txtEndCash.Value = "0.00" Then
MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtEndCash.Value) Then
MsgBox "Hey, dummy! The END CASH Amount must be a NUMBER!",
vbExclamation, "Money Error"
Me.txtEndCash.SetFocus
Exit Sub
End If

End Sub

Private Sub UserForm_Click()

End Sub

--------

Thanks in advance for any help!

--

Dave Peterson

ComboBox search for variable...
 

On Jan 17, 10:49*am, Dave Peterson wrote:
Maybe you can fit this into your code:

Dim res as variant
dim myRng as range

with worksheets(TheSheet)
* *set myrng = .range("a6:a37")
* *'cmbdate.value is text. *
* *'I'm guessing that the values in A6:A37 are really numbers
* *res = application.match(clng(cmbdate.value),myrng,0)
* *if iserror(res) then
* * * msgbox "That date isn't on " & .name & "!"
* *else
* * * myrng(res).offset(0,3).value = txtEndCash 'not txt.endcash???
* *end if
end with





Señor Rubia wrote:

I am doing VBA for a spreadsheet at work. *The idea behind this
spreadsheet is to have a UserForm that asks for a certain Month
(cmbMonth) & Day (cmbDate) and Dollar Amount (txt.EndCash). Then,
using the Month, it goes to a certain sheet (named "January",
"February", etc.) and finds the cell with the specific date already in
there, then goes 3 cells to the right to input the dollar amount
specified. *So far, I have succeeded in code that specifies the sheet,
but have drawn a blank on something that can assign a variable to
cmbDate and search for it on the sheet in A6:A37.

Here's my current code if that helps out any (most of it applies to
the OK, Cancel & Clear buttons, obviously:

-----------------------------------------
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
* * For Each ctl In Me.Controls
* * * * If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox"
Then
* * * * * * ctl.Value = ""
* * * * ElseIf TypeName(ctl) = "CheckBox" Then
* * * * ctl.Value = False
* * * * End If
Next ctl
End Sub

Private Sub cmdOK_Click()

* * ScreenUpdating = False
* * Val1 = txtEndCash
* * TheSheet = cmbMonth.Value
* * Worksheets(TheSheet).Activate
* * ActiveSheet.Range("H3") = Val1
* * ScreenUpdating = True

Dim RowCount As Long
* * RowCount =
Worksheets("January").Range("D20").CurrentRegion.R ows.Count
* * With Worksheets("January").Range("D20")
* * * * .Offset(RowCount, 0).Value = Me.txtEndCash.Value
* * End With

* * If Me.cmbMonth.Value = "" Then
* * * * MsgBox "Please Enter A Month!", vbExclamation, "Date Error"
* * * * Me.cmbMonth.SetFocus
* * * * Exit Sub
* * End If

* * If Me.cmbDate.Value = "" Then
* * * * MsgBox "Please Enter A Date!", vbExclamation, "Date Error"
* * * * Me.cmbDate.SetFocus
* * * * Exit Sub
* * End If

* * If Me.txtEndCash.Value = "" Then
* * * * MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
* * * * Me.txtEndCash.SetFocus
* * * * Exit Sub
* * End If

* * If Me.txtEndCash.Value = "0.00" Then
* * * * MsgBox "Please Enter A Dollar Amount!", vbExclamation, "Money
Error"
* * * * Me.txtEndCash.SetFocus
* * * * Exit Sub
* * End If

* * If Not IsNumeric(Me.txtEndCash.Value) Then
* * * * MsgBox "Hey, dummy! *The END CASH Amount must be a NUMBER!",
vbExclamation, "Money Error"
* * * * Me.txtEndCash.SetFocus
* * * * Exit Sub
* * End If

End Sub

Private Sub UserForm_Click()

End Sub

--------

Thanks in advance for any help!

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Seems to work good. Thanks!!