-
selecting variable to declare a format
hi, i've got a program that gives particular currency formats in required cells. they are found from a userform, with four options.
Code:
[VBA]
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
[/VBA]
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...
-
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
There is an excellent explanation of cross-posting available here: http://www.excelguru.ca/XLKBA/XLKBA05.htm
-
sorry guys, thanks for the help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules