Force cell to have Accounting Formate
 

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

Force cell to have Accounting Formate
 

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

Force cell to have Accounting Formate
 

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

Force cell to have Accounting Formate
 

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