currency in vba userform

H

harrar

Trying to set up a userform to enter weekly sales figures for various items
i.e, bottles,cans, plastic with text boxes used for dollar amount input and
then dump the data into a the spreadsheet. The sheet cells are formated as
"currency" but when I run the userform the data is sent as text, the cells
display i.e 2000 and an error message appears relating to format.

Tried to DIM the variables and .Value [currency] to no avail. All the
spreadsheet shows $0.00 irregardless of what was entered in the userform.

I think the problem may be Property Set related but I'm not that experienced
with it but then again it could be something really simple I'm missing.

Any help provided would be greatly appreciated.

Thanks in advance
 
B

Bob Phillips

Show the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

harrar

Sorry about that, Bob
Here's a sample of the coding for the userform:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdEnter_Click()
Sheet1.Select
If combWeek = "1" Then
Range("b6").Value = tbLiq
Range("c6").Value = tbDraft
Range("d6").Value = tbBottle
Range("e6").Value = tbCan
Range("f6").Value = tbWine
Range("g6").Value = tbSoda
ElseIf combWeek = "2" Then
Range("b15").Value = tbLiq
Range("c15").Value = tbDraft
Range("d15").Value = tbBottle
Range("e15").Value = tbCan
Range("f15").Value = tbWine
Range("g15").Value = tbSoda
ElseIf combWeek = "3" Then
Range("b25").Value = tbLiq
Range("c25").Value = tbDraft
Range("d25").Value = tbBottle
Range("e25").Value = tbCan
Range("f25").Value = tbWine
Range("g25").Value = tbSoda
ElseIf combWeek = "4" Then
Range("b35").Value = tbLiq
Range("c35").Value = tbDraft
Range("d35").Value = tbBottle
Range("e35").Value = tbCan
Range("f35").Value = tbWine
Range("g35").Value = tbSoda
ElseIf combWeek = "5" Then
Range("b45").Value = tbLiq
Range("c45").Value = tbDraft
Range("d45").Value = tbBottle
Range("e45").Value = tbCanned
Range("f45").Value = tbWine
Range("g45").Value = tbSoda
ElseIf combWeek = "6" Then
Range("b55").Value = tbLiq
Range("c55").Value = tbDraft
Range("d55").Value = tbBottle
Range("e55").Value = tbCanned
Range("f55").Value = tbWine
Range("g55").Value = tbSoda

End If
End Sub
Private Sub UserForm_Initialize()

tbLiq.Value = "0.00"
tbDraft.Value = "0.00"
tbBottle.Value = "0.00"
tbCan.Value = "0.00"
tbWine.Value = "0.00"
tbSoda.Value = "0.00"

With combWeek
..AddItem "1"
..AddItem "2"
..AddItem "3"
..AddItem "4"
..AddItem "5"
..AddItem "6"
..AddItem "7"
..AddItem "8"
..AddItem "9"
..AddItem "10"
..AddItem "11"
End With
combWeek.Value = "1"
combWeek.SetFocus

End Sub


Bob Phillips said:
Show the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


harrar said:
Trying to set up a userform to enter weekly sales figures for various items
i.e, bottles,cans, plastic with text boxes used for dollar amount input and
then dump the data into a the spreadsheet. The sheet cells are formated as
"currency" but when I run the userform the data is sent as text, the cells
display i.e 2000 and an error message appears relating to format.

Tried to DIM the variables and .Value [currency] to no avail. All the
spreadsheet shows $0.00 irregardless of what was entered in the userform.

I think the problem may be Property Set related but I'm not that experienced
with it but then again it could be something really simple I'm missing.

Any help provided would be greatly appreciated.

Thanks in advance
Click to expand...
Click to expand...
 
B

Bob Phillips

Is the problem with the Cans on week 5 & 6? You have a wrong textbox name
there, you use tbCanned where elsewhere you use tbCan.

If you used Option Explicit in your code, that would not be a problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)


harrar said:
Sorry about that, Bob
Here's a sample of the coding for the userform:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdEnter_Click()
Sheet1.Select
If combWeek = "1" Then
Range("b6").Value = tbLiq
Range("c6").Value = tbDraft
Range("d6").Value = tbBottle
Range("e6").Value = tbCan
Range("f6").Value = tbWine
Range("g6").Value = tbSoda
ElseIf combWeek = "2" Then
Range("b15").Value = tbLiq
Range("c15").Value = tbDraft
Range("d15").Value = tbBottle
Range("e15").Value = tbCan
Range("f15").Value = tbWine
Range("g15").Value = tbSoda
ElseIf combWeek = "3" Then
Range("b25").Value = tbLiq
Range("c25").Value = tbDraft
Range("d25").Value = tbBottle
Range("e25").Value = tbCan
Range("f25").Value = tbWine
Range("g25").Value = tbSoda
ElseIf combWeek = "4" Then
Range("b35").Value = tbLiq
Range("c35").Value = tbDraft
Range("d35").Value = tbBottle
Range("e35").Value = tbCan
Range("f35").Value = tbWine
Range("g35").Value = tbSoda
ElseIf combWeek = "5" Then
Range("b45").Value = tbLiq
Range("c45").Value = tbDraft
Range("d45").Value = tbBottle
Range("e45").Value = tbCanned
Range("f45").Value = tbWine
Range("g45").Value = tbSoda
ElseIf combWeek = "6" Then
Range("b55").Value = tbLiq
Range("c55").Value = tbDraft
Range("d55").Value = tbBottle
Range("e55").Value = tbCanned
Range("f55").Value = tbWine
Range("g55").Value = tbSoda

End If
End Sub
Private Sub UserForm_Initialize()

tbLiq.Value = "0.00"
tbDraft.Value = "0.00"
tbBottle.Value = "0.00"
tbCan.Value = "0.00"
tbWine.Value = "0.00"
tbSoda.Value = "0.00"

With combWeek
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
End With
combWeek.Value = "1"
combWeek.SetFocus

End Sub


Bob Phillips said:
Show the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


harrar said:
Trying to set up a userform to enter weekly sales figures for various items
i.e, bottles,cans, plastic with text boxes used for dollar amount
Click to expand...
input
and
then dump the data into a the spreadsheet. The sheet cells are
Click to expand...
formated
as
"currency" but when I run the userform the data is sent as text, the cells
display i.e 2000 and an error message appears relating to format.

Tried to DIM the variables and .Value [currency] to no avail. All the
spreadsheet shows $0.00 irregardless of what was entered in the userform.

I think the problem may be Property Set related but I'm not that experienced
with it but then again it could be something really simple I'm missing.

Any help provided would be greatly appreciated.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
H

harrar

No, the problem is with every entry. It is sent from the form to the sheet
as text. Thanks for the Option Explicit though. I'll incorporate that now.

Bob Phillips said:
Is the problem with the Cans on week 5 & 6? You have a wrong textbox name
there, you use tbCanned where elsewhere you use tbCan.

If you used Option Explicit in your code, that would not be a problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)


harrar said:
Sorry about that, Bob
Here's a sample of the coding for the userform:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdEnter_Click()
Sheet1.Select
If combWeek = "1" Then
Range("b6").Value = tbLiq
Range("c6").Value = tbDraft
Range("d6").Value = tbBottle
Range("e6").Value = tbCan
Range("f6").Value = tbWine
Range("g6").Value = tbSoda
ElseIf combWeek = "2" Then
Range("b15").Value = tbLiq
Range("c15").Value = tbDraft
Range("d15").Value = tbBottle
Range("e15").Value = tbCan
Range("f15").Value = tbWine
Range("g15").Value = tbSoda
ElseIf combWeek = "3" Then
Range("b25").Value = tbLiq
Range("c25").Value = tbDraft
Range("d25").Value = tbBottle
Range("e25").Value = tbCan
Range("f25").Value = tbWine
Range("g25").Value = tbSoda
ElseIf combWeek = "4" Then
Range("b35").Value = tbLiq
Range("c35").Value = tbDraft
Range("d35").Value = tbBottle
Range("e35").Value = tbCan
Range("f35").Value = tbWine
Range("g35").Value = tbSoda
ElseIf combWeek = "5" Then
Range("b45").Value = tbLiq
Range("c45").Value = tbDraft
Range("d45").Value = tbBottle
Range("e45").Value = tbCanned
Range("f45").Value = tbWine
Range("g45").Value = tbSoda
ElseIf combWeek = "6" Then
Range("b55").Value = tbLiq
Range("c55").Value = tbDraft
Range("d55").Value = tbBottle
Range("e55").Value = tbCanned
Range("f55").Value = tbWine
Range("g55").Value = tbSoda

End If
End Sub
Private Sub UserForm_Initialize()

tbLiq.Value = "0.00"
tbDraft.Value = "0.00"
tbBottle.Value = "0.00"
tbCan.Value = "0.00"
tbWine.Value = "0.00"
tbSoda.Value = "0.00"

With combWeek
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
End With
combWeek.Value = "1"
combWeek.SetFocus

End Sub


Bob Phillips said:
Show the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Trying to set up a userform to enter weekly sales figures for various
items
i.e, bottles,cans, plastic with text boxes used for dollar amount input
and
then dump the data into a the spreadsheet. The sheet cells are formated
as
"currency" but when I run the userform the data is sent as text, the cells
display i.e 2000 and an error message appears relating to format.

Tried to DIM the variables and .Value [currency] to no avail. All the
spreadsheet shows $0.00 irregardless of what was entered in the userform.

I think the problem may be Property Set related but I'm not that
experienced
with it but then again it could be something really simple I'm missing.

Any help provided would be greatly appreciated.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA currency or percent format object 1
Odd behaviour with a userform 0
Userform Text box Format to Currency 2
Question about modal userform..... 2
OFFSET LOOKUP function in VBA? 9
Having Trouble Getting My Userform to Populate the Corresponding Excel Spreadsheet... 1
Excel data display userform problem 3
Formating textbox's on a userform 2

Top