Run different Macros based on what selections have been made in checkbox

**vetteengr** · 10-22-2012, 11:57 PM

Hi,
I am creating a BoM table. There are different option content for the product. I have a set of checkbox (Form) that I have on the first tab. For instance, the product can have
1. 7.6Kwh
2. 15 Kwh
3. 23 Kwh

the second set of options are:
1. HVAC
2. Heater Only

the third set of options are:
1. Red
2. White
3. Blue

I have separate tabs for each of these option sets and I have created a blank template sheet. I want the macro to populate the blank template based on the selection made on the first page. For instance, if I select 7.6, Heater and White, I want the macro to copy the info from those 3 tabs and paste them in the blank template.

Can this be done?

Appreciate the help...

**brynbaker** · 10-23-2012, 06:55 AM

Assuming the data you populate the 3rd sheet with comes from the other tabs for each option an example spreadsheet would facilitate a detailed response.
It certainlyy sounds simple enough.

**vetteengr** · 10-23-2012, 08:20 AM

Hi Brynbaker,
Thanx for the response. Yes, the data are in different tabs. I am attaching the file for your review. I plan on showing only the "Welcome" and the "BoM" tabs to the users. They will select the options they want from the Welcome screen and when they click the command button I want it to run the macros based on the selections made.

Thank you again.

**brynbaker** · 11-07-2012, 03:58 AM

Apologies for the delay; this PC has Office 2010 but rejects downloads from the forum; my home PC has 2003. I have another option that I will try as soon as possible.

**vetteengr** · 11-08-2012, 11:16 PM

Originally Posted by brynbaker

Apologies for the delay; this PC has Office 2010 but rejects downloads from the forum; my home PC has 2003. I have another option that I will try as soon as possible.

Hi Brynbaker,
Appreciate your help. Looking forward to your comments.

**brynbaker** · 11-12-2012, 02:46 PM

I had not forgotten, but I have been thwarted in my attempt: A major domestic upheaval has mislaid (I hope that's the right word) my laptop. I hav unable
Incidentally it may be time to update your details; the stuff at left of your posts says 2003 but you've uplaoded a .xslx file? I have managed to load part in 2003 and it may be enough though the conversion listed 4 problems. Before I get timed out I will pause and read what i loaded...

**brynbaker** · 11-12-2012, 03:39 PM

Hmm. Well I see that you have used the macro recorder to develop some of the code - right on! More people should try that. But it's wise to read and tidy what you get, eliminating the unnecessary (for example spurious "Ramg3("A6".Select") when you click a cell you don't really need to while recording.
Anyway it is a bit hard to follow. And I don't use userforms (mo-one has shown me any advantage in simple design). But I do know that options usually require an option button so that (for instance) one cannot order a car that both has and ddoes not have a heater!

Apart from these slim pieces of advice, I think the issue about loading the unconverted file in the correct Excel version prevents me from helping you further at present. I hope another member is better placed to help. I will monitor the thread in case I can do more.

**vetteengr** · 11-12-2012, 10:16 PM

Hi,
Thank you for your comments. Yes, i mostly used the recorder and a bit of help from other members to create some of the VB. I can save the file in a earlier format if that helps. Appreciate any help.

Regards

**vetteengr** · 11-12-2012, 10:17 PM

And I apologize, my Excel is Excel 2010

**SuitedAces** · 11-13-2012, 01:12 AM

I also have 2003, so I cannot view your file.
But in general your code will be a lot more straightforward if you use listboxes for those choices.
I would also ask why you have separate tabs for those options.

**SuitedAces** · 11-13-2012, 02:14 AM

Here is a code example of how you might handle that with 3 groups of option buttons (instead of check boxes) on the same tab

Create 3 option buttons named wattage_1, wattage_2,wattage_3
Set the value of wattage_1 = true
Give them all the same group name. (ex wattage)
The name won't matter

Create 2 option buttons named type_1, type_2 (for hvac or heater)
Set the value of type_1 = true
Give those two the same group name (ex type)

Create 3 option buttons named color_1, color_2,color_3
Set the value of color_1 = true
Give them all the same group name. (ex color)

Put a control button on the form
Use this code

Dim wattage As Integer
Dim HVAC_or_Heater As Integer
Dim color As Integer
Private Sub CommandButton1_Click()
'do something based on the 3 variables
'EXAMPLE:
Range("A1") = Choose(wattage, "7.6Kwh", "15 Kwh", "23 Kwh")
Range("A2") = Choose(HVAC_or_Heater, "HVAC", "Heater Only")
Range("A3") = Choose(color, "Red", "White", "Blue")
End Sub
Private Sub UserForm_Initialize()
    wattage = 1
    HVAC_or_Heater = 1
    color = 1
End Sub
Private Sub wattage_1_Click()
wattage = 1
End Sub
Private Sub wattage_2_Click()
wattage = 2
End Sub
Private Sub wattage_3_Click()
wattage = 3
End Sub
Private Sub type_1_Click()
HVAC_or_Heater = 1
End Sub
Private Sub type_2_Click()
HVAC_or_Heater = 2
End Sub
Private Sub color1_Click()
color = 1
End Sub
Private Sub color2_Click()
color = 2
End Sub
Private Sub color3_Click()
color = 3
End Sub

When you hit the command button (call it the enter button) you'll have the wattage, type and color variables available in your code.

**jaslake** · 11-13-2012, 12:26 PM

Hi vetteengr

I've taken the liberty of rewriting the code for Sub Battery7_6Kwh() and Sub Battery15Kwh().

Sub Battery7_6Kwh() is assigned to Check7-6 and Sub Battery15Kwh is assigned to Check Box 2. The respective Code will run when the Check Box is clicked.

Let me know of issues.

**brynbaker** · 11-13-2012, 01:13 PM

John thanks for your input. I diid look up Atwood Lake: very nice. I'm jealous.

Run different Macros based on what selections have been made in checkbox

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Re: Run different Macros based on what selections have been made in checkbox

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions