Cool Toggle Button

Topic

New Reply

Hi,

I’m in need of assistance, this VBA is tough but so interesting!

I have the following code but was looking to make it neater /  shorter. My attempts at this have been less than fruitful so i was wondering if anyone out there would know how to get this looking better, it functions as desired (Cycles between ft and metres) but if i could get an actual Range “B3:G9” without having to write all of this it would be great.

Any help is appreciated

Thanks

 

‘Toggles between Metres and Feet

Option Explicit

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then
ToggleButton1.Caption = “Meters”
ToggleButton1.BackColor = RGB(0, 176, 80)
Range(“B3:G9”).Font.Color = RGB(0, 176, 80)

Range(“B3”) = Range(“B3”) / 3.2808
Range(“C3”) = Range(“C3”) / 3.2808
Range(“D3”) = Range(“D3”) / 3.2808

Range(“B4”) = Range(“B4”) / 3.2808
Range(“C4”) = Range(“C4”) / 3.2808
Range(“D4”) = Range(“D4”) / 3.2808

Range(“B5”) = Range(“B5”) / 3.2808
Range(“C5”) = Range(“C5”) / 3.2808
Range(“D5”) = Range(“D5”) / 3.2808

Range(“B6”) = Range(“B6”) / 3.2808
Range(“C6”) = Range(“C6”) / 3.2808
Range(“D6”) = Range(“D6”) / 3.2808

Range(“B7”) = Range(“B7”) / 3.2808
Range(“C7”) = Range(“C7”) / 3.2808
Range(“D7”) = Range(“D7”) / 3.2808

Range(“B8”) = Range(“B8”) / 3.2808
Range(“C8”) = Range(“C8”) / 3.2808
Range(“D8”) = Range(“D8”) / 3.2808

Range(“B9”) = Range(“B9”) / 3.2808
Range(“C9”) = Range(“C9”) / 3.2808
Range(“D9”) = Range(“D9”) / 3.2808

ElseIf Range(“B3, C3, D3, B4,C4, D4,B5,C5,D6”) = Range(“B3, C3, D3, B4,C4, D4,B5,C5,D6”) = True Then

ToggleButton1.Caption = “Feet”
ToggleButton1.BackColor = vbCyan
Range(“B3:G9”).Font.Color = RGB(0, 0, 255)

Range(“B3”) = Range(“B3”) * 3.2808
Range(“C3”) = Range(“C3”) * 3.2808
Range(“D3”) = Range(“D3”) * 3.2808

Range(“B4”) = Range(“B4”) * 3.2808
Range(“C4”) = Range(“C4”) * 3.2808
Range(“D4”) = Range(“D4”) * 3.2808

Range(“B5”) = Range(“B5”) * 3.2808
Range(“C5”) = Range(“C5”) * 3.2808
Range(“D5”) = Range(“D5”) * 3.2808

Range(“B6”) = Range(“B6”) * 3.2808
Range(“C6”) = Range(“C6”) * 3.2808
Range(“D6”) = Range(“D6”) * 3.2808

Range(“B7”) = Range(“B7”) * 3.2808
Range(“C7”) = Range(“C7”) * 3.2808
Range(“D7”) = Range(“D7”) * 3.2808

Range(“B8”) = Range(“B8”) * 3.2808
Range(“C8”) = Range(“C8”) * 3.2808
Range(“D8”) = Range(“D8”) * 3.2808

Range(“B9”) = Range(“B9”) * 3.2808
Range(“C9”) = Range(“C9”) * 3.2808
Range(“D9”) = Range(“D9”) * 3.2808

End If

End Sub

Reply | Quote

Replies

Something like this (untested) should work.

for each C in (“B”,”C”,” D”)
for R = 3 to 9
Range(C & R) = Range(C & R) / 3.2808
next R
next C

cheers, Paul

1 user thanked author for this post.

WSAbsinthe

Reply | Quote

Hi

You can use vba loops to process a block of cells as suggested by Paul, or perhaps use the vba PasteSpecial method which allows you to copy a cell and then multiply a whole block of cells with that value. Ditto for dividing the values in a block of cell..

Private Sub ToggleButton1_Click()

Application.ScreenUpdating = False          'freeze display till ready

If ToggleButton1.Caption = "Feet" Then      'currently showing feet, so..
ToggleButton1.BackColor = RGB(0, 176, 80)   'change button colour
ToggleButton1.Caption = "Meters"            'change button caption

[G1].Copy                                   'copy cell with scale e.g. 3.2808
[B3:D9].PasteSpecial Operation:=xlDivide    'convert data range to Meters
[B3:D9].Font.Color = RGB(0, 176, 80)        'change font colour

Else                                        'otherwise..
ToggleButton1.Caption = "Feet"              'change button caption text
ToggleButton1.BackColor = vbCyan            'change button colour

[G1].Copy                                   'copy cell with scale e.g. 3.2808
[B3:D9].PasteSpecial Operation:=xlMultiply  'convert data range to Feet
[B3:D9].Font.Color = RGB(0, 0, 255)         'change font colour

End If                                      'end of test for button caption

Application.CutCopyMode = False             'cancel copy-to range
[A1].Select                                 'put cellpointer in tidy location

End Sub

I have added this code in the attached .xls file.
Personally, I would avoid using ActiveX buttons on a worksheet, and would rather use FormControl buttons, but hey ho, if it works that’s OK.
I added another sheet with some additional methods of converting values from one to another.

zeddy
Excel Management Hostility Unit

zeddy-1a

1 user thanked author for this post.

WSAbsinthe

Reply | Quote

WSAbsinthe,

Did you say compact?

Private Sub ToggleButton1_Click()

Application.ScreenUpdating = False          'freeze display till ready

ToggleButton1.Caption = IIf(ToggleButton1.Caption = "Feet", "Meters", "Feet") 'Change Caption
ToggleButton1.BackColor = IIf(ToggleButton1.Caption = "Meters", RGB(0, 176, 80), RGB(0, 255, 255)) 'change button colour
[G1].Copy                                   'copy cell with scale e.g. 3.2808
[B3:D9].PasteSpecial Operation:=IIf(ToggleButton1.Caption = "Meters", xlDivide, xlMultiply)   'convert data range
[B3:D9].Font.Color = IIf(ToggleButton1.Caption = "Meters", RGB(0, 176, 80), RGB(0, 0, 255))   'change font colour
Application.CutCopyMode = False             'cancel copy-to range
[A1].Select                                 'put cellpointer in tidy location

End Sub

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

2 users thanked author for this post.

WSAbsinthe, zeddy

Reply | Quote

[zeddy]

Hi RG

A truly elegant, sophisticated and compact response.
I would expect nothing less!

I sometimes get confused with the vba Immediate If (IFF), the mathematical If and only If (IFF), and the radar IFF (Identification, friend or foe).
Thanks for the reminder, friend.
Hope you are fit and well!

zeddy
Excel Biconditional Logical Operator

• This reply was modified 5 years, 9 months ago by zeddy.

Reply | Quote