Create Toggle Buttons that change color and change other cell values

[epmd]

Hello all, I am new to VBA and am trying to figure out how to create buttons that perform certain functions.
I am creating a product information page that provides specs for a given product. By default, all information should display in English units. In addition, the button "English" under Measurements in column E should automatically be gray (White, Background 1, Darker 35%/ Hex: #A6A6A6), inner bevel with white text. If the user clicks Metric (column F), then the following actions should happen:

1. Format of the English and Metric buttons should swap.
2. Calculations for the dimensions in columns B - D should be converted to metric units.
3. Text in column A should change to represent metric units.
4. If the user clicks the English button again, it should revert back to the original state. However, clicking the already depressed button should not result in any additional actions.

For reference, the cell range pasted above is A10 to F13. Buttons are in E12 and F12. The Metric button is outer bevel with black text on a gold button (Gold, Accent 4, Lighter 40%/ Hex: #FFD966).

I appreciate any assistance with this!

 

[PeteWright]

I've created an Excel File with VBA (<- download left), is this what you need?

 

[epmd]

I've created an Excel File with VBA (<- download left), is this what you need?


View attachment 51674

Pete, thank you! Looking at the gif, this is exactly what I wanted it to do. I just downloaded and will let you know if there are any problems, but it looks perfect!

 

[epmd]

I've created an Excel File with VBA (<- download left), is this what you need?


View attachment 51674

Pete, I downloaded your file, and everything worked perfectly. However, when I tried to adapt the code for my workbook, things went haywire and I was unable to use Toggle buttons. When I tried to add the macro for the convert functions to the existing "buttons" I had created, I got an "undefined" error. So I went to the developer tab, clicked to Insert Toggle Button, but then it tries to attach an "EMBED..." function, which creates a fatal Excel error. Not sure what I'm doing wrong in adapting this.

 

[PeteWright]

Pete, I downloaded your file, and everything worked perfectly. However, when I tried to adapt the code for my workbook, things went haywire and I was unable to use Toggle buttons. When I tried to add the macro for the convert functions to the existing "buttons" I had created, I got an "undefined" error. So I went to the developer tab, clicked to Insert Toggle Button, but then it tries to attach an "EMBED..." function, which creates a fatal Excel error. Not sure what I'm doing wrong in adapting this.

Hi @epmd and sorry for the late response.
Do you still need help with the VBA code?
Please let me know here.

 

[epmd]

Hi @epmd and sorry for the late response.
Do you still need help with the VBA code?
Please let me know here.

Hi @PeteWright, yes, I still need some help with this. I wasn't able to figure out the issue with why I couldn't adapt your code to my spreadsheets. Please note that I am using Excel 365.

I have tried it using button shapes with assigned macros, and I get a VB error, Run-time error 9 when I assign the functions to the appropriate buttons.

The Debugger gives an error with this line:
If ThisWorkbook.Sheets("Sheet4").Toggle0.Value = True And ThisWorkbook.Sheets("Sheet4").Toggle1.Value = False Then


I have copied the mini-sheet below from my original workbook. I modified the VBA code to reflect the actual sheet and cell locations in my workbook, but when I clicked to Insert Toggle Button instead of assigning a macro to a button shape, Excel tries to attach an "EMBED..." function, which creates a fatal error.

ConvertTest.xlsm
	A	B	C	D	E	F
11	Product Information	Unit* (Net - oz.)	Inner Pack	Case Pack	Measurements
12	Weight	12		10.987
13	Dimensions (L X W X H) (in.)	2.1875 x 2.1875 x 8.6875		9.25 x 7 x 9.313	English	Metric
14	Volume (cu.ft.)	0.0241		0.3490
User Interface by SKU