I have a VBA code to apply values from a UserForm to a Worksheet. My code
inserts the Label Control Captino into the Cell. How can I force the cell to
have an accounting format.

Private Sub cmbApplyTax_Click()

Dim mySalesTax As Range
Dim myRow As Long
Dim mySubTotal As Double, myFreight As Double

'finds Sales Tax cell on the QUOTE sheet
Set mySalesTax = Sheets("QUOTE").Columns("E:E").Find(What:="Sales Tax", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Sheets("QUOTE").Unprotect "AdTech"

'show tax exempt
If optTaxExempt = True Then
mySalesTax.Offset(0, 1).Value = "Tax Exempt"
End If

'adds 6% sales tax
If optSalesTax6 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax6
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

'adds 7% sales tax
If optSalesTax7 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax7
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

Sheets("QUOTE").Protect "AdTech"

Unload Me

End Sub

Replace your line:
Range("F7:F8").NumberFormat = "Accounting" '<==Error Error
with this line:
Range("F7:F8").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)" '<==Error Error

This will show values in Dollar format without the dollar sign. (the above
string is one line).

HTH
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"RyanH" wrote:

I have a VBA code to apply values from a UserForm to a Worksheet. My code
inserts the Label Control Captino into the Cell. How can I force the cell to
have an accounting format.

Private Sub cmbApplyTax_Click()

Dim mySalesTax As Range
Dim myRow As Long
Dim mySubTotal As Double, myFreight As Double

'finds Sales Tax cell on the QUOTE sheet
Set mySalesTax = Sheets("QUOTE").Columns("E:E").Find(What:="Sales Tax", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Sheets("QUOTE").Unprotect "AdTech"

'show tax exempt
If optTaxExempt = True Then
mySalesTax.Offset(0, 1).Value = "Tax Exempt"
End If

'adds 6% sales tax
If optSalesTax6 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax6
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

'adds 7% sales tax
If optSalesTax7 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax7
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

Sheets("QUOTE").Protect "AdTech"

Unload Me

End Sub

I tried that already, but with the $ in front. The problem is it displays
the dollar amount as if it is formatted in Currency. For Example, it shows
$500.00(Currency), instead of $ 500.00 (Accounting).

Is it possible to display it like the Accounting example?

Thanks in Advance,
Ryan

"Rick S." wrote:

Replace your line:
Range("F7:F8").NumberFormat = "Accounting" '<==Error Error
with this line:
Range("F7:F8").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)" '<==Error Error

This will show values in Dollar format without the dollar sign. (the above
string is one line).

HTH
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"RyanH" wrote:

I have a VBA code to apply values from a UserForm to a Worksheet. My code
inserts the Label Control Captino into the Cell. How can I force the cell to
have an accounting format.

Private Sub cmbApplyTax_Click()

Dim mySalesTax As Range
Dim myRow As Long
Dim mySubTotal As Double, myFreight As Double

'finds Sales Tax cell on the QUOTE sheet
Set mySalesTax = Sheets("QUOTE").Columns("E:E").Find(What:="Sales Tax", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Sheets("QUOTE").Unprotect "AdTech"

'show tax exempt
If optTaxExempt = True Then
mySalesTax.Offset(0, 1).Value = "Tax Exempt"
End If

'adds 6% sales tax
If optSalesTax6 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax6
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

'adds 7% sales tax
If optSalesTax7 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax7
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

Sheets("QUOTE").Protect "AdTech"

Unload Me

End Sub

As long as the cell width is greater than the text it will visually appear
with a seperation between the "$" and the actual "1.00".
If I make the cell width 100, there is a huge seperation of the "$" and the
value.

I guess, I am not following. It appears you want approximately two spaces
between the "$" and the value?
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"RyanH" wrote:

I tried that already, but with the $ in front. The problem is it displays
the dollar amount as if it is formatted in Currency. For Example, it shows
$500.00(Currency), instead of $ 500.00 (Accounting).

Is it possible to display it like the Accounting example?

Thanks in Advance,
Ryan

"Rick S." wrote:

Replace your line:
Range("F7:F8").NumberFormat = "Accounting" '<==Error Error
with this line:
Range("F7:F8").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)" '<==Error Error

This will show values in Dollar format without the dollar sign. (the above
string is one line).

HTH
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"RyanH" wrote:

I have a VBA code to apply values from a UserForm to a Worksheet. My code
inserts the Label Control Captino into the Cell. How can I force the cell to
have an accounting format.

Private Sub cmbApplyTax_Click()

Dim mySalesTax As Range
Dim myRow As Long
Dim mySubTotal As Double, myFreight As Double

'finds Sales Tax cell on the QUOTE sheet
Set mySalesTax = Sheets("QUOTE").Columns("E:E").Find(What:="Sales Tax", _
After:=Cells(6, 5), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Sheets("QUOTE").Unprotect "AdTech"

'show tax exempt
If optTaxExempt = True Then
mySalesTax.Offset(0, 1).Value = "Tax Exempt"
End If

'adds 6% sales tax
If optSalesTax6 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax6
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

'adds 7% sales tax
If optSalesTax7 = True Then
mySalesTax.Offset(0, 1) = lblSalesTax7
Range("F7:F8").NumberFormat = "Accounting" <==Error Error
End If

Sheets("QUOTE").Protect "AdTech"

Unload Me

End Sub