selecting variable to declare a format

[rodney_malod]

hi, i've got a program that gives particular currency formats in required cells. they are found from a userform, with four options.

Code:


Private Sub CommandButton1_Click()
'


Dim format As Currency
Dim text1 As String
Dim text2 As String

If OptionButton1 = True Then

text1 = "Total Sales Price £"
text2 = "List Price £"
format = "$#,##0.00"
Else
If OptionButton2 = True Then

text1 = "Total Sales Price €"
text2 = "List Price €"
format = "[$€-2] #,##0.00"
Else
If OptionButton3 = True Then

text1 = "Total Sales Price DKK"
text2 = "List Price DKK"
format = "[$DKK] #,##0.00"
Else
If OptionButton4 = True Then

text1 = "Total Sales Price $"
text2 = "List Price $"
format = "[$$-409]#,##0.00"

Application.EnableEvents = False

Sheets("Summary Sheet").Select
ActiveSheet.Unprotect
Range("c13:c44").Select
Selection.NumberFormat = format
Range("C47").Select
Selection.NumberFormat = format
Range("C49").Select
Selection.NumberFormat = format
ActiveSheet.Protect
'
Sheets("sales sheet (1)").Select
ActiveSheet.Unprotect
Range("H154").Select
Selection.NumberFormat = format
Range("H8").Select
ActiveCell = text1
Range("G8").Select
ActiveCell = text2
Range("G17:H153").Select
Selection.NumberFormat = format
ActiveSheet.Protect

'
UserForm2.Hide
'
Sheets("Cover Sheet").Select
'
Application.ScreenUpdating = True
'
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

basically my problem lies in that i can't select a currency variable to declare as.

if only there was a "dim ~~~ as format"

i hope i haven't confused anyone... http://www.mrexcel.com/board2/images/smiles/icon_confused.gif

[mdmackillop]

Hi Rodney,
Two things to do.
Change Dim statement to - Dim format as string
Move your 4 End If lines before this line
Application.EnableEvents = False
Regards
MD

[mdmackillop]

Here's a neater way of doing your formatting. It will change with the option button, instead of the command button and avoids the need to select cells. While this won't appear much quicker with a small macro, it will help with a large number of changes. The code passes the variable values to another sub which does the task, making it much easier to add additional option buttons if required.
Regards
MD


Private Sub OptionButton1_Click()
DoFormat "$#,##0.00", "£"
End Sub
Private Sub OptionButton2_Click()
DoFormat "[$€-2] #,##0.00", "€"
End Sub
Private Sub OptionButton3_Click()
DoFormat "[$DKK] #,##0.00", "DKK"
End Sub
Private Sub OptionButton4_Click()
DoFormat "[$$-409]#,##0.00", "$"
End Sub
Private Sub DoFormat(Format As String, Curr As String)

Dim text1 As String
Dim text2 As String

text1 = "Total Sales Price " & Curr
text2 = "List Price " & Curr

Application.ScreenUpdating = False
Application.EnableEvents = False

With Sheets("Summary Sheet")
.Unprotect
.Range("C13:C44,C47,C49").NumberFormat = Format
.Protect
End With

With Sheets("sales sheet (1)")
.Unprotect
.Range("H8") = text1
.Range("G8") = text2
.Range("G17:H153,H154").NumberFormat = Format
.Protect
End With

UserForm2.Hide
Sheets("Cover Sheet").Activate
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[geekgirlau]

Hi Rodney,

If you post the same question on more than one board, please make sure you create a link to the original post.

http://www.mrexcel.com/board2/viewtopic.php?t=199381 (http://www.mrexcel.com/board2/viewtopic.php?t=199381)

There is an excellent explanation of cross-posting available here: http://www.excelguru.ca/XLKBA/XLKBA05.htm

[rodney_malod]

sorry guys, thanks for the help.