 |
BOOK: Excel VBA 24-Hour Trainer
 | This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer by Tom Urtis; ISBN: 978-0-470-89069-1 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the BOOK: Excel VBA 24-Hour Trainer section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

December 30th, 2015, 11:42 AM
|
Registered User
|
|
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Activate specific sheet with ComboBox
Good morning everyone,
I am completely unexperience regarding macros programming and I was wondering if someone can help me.
I create a ComboBox in which I want to activate a specific sheet to do a calculation in it.
So far, I could create the combobox with his contain. Please see below.
Sub ComboBox()
Dim Rangé As Integer
Dim ListCombo As Variant
Rangé = 1
'Sheet22.ComboBox1.Clear
While (Worksheets("Sheet1").Range("A" & CStr(Rangé)) <> "")
Sheet22.ComboBox1.AddItem Worksheets("Sheet1").Range("A" & CStr(Rangé))
Rangé = Rangé + 1
Wend
ListCombo = Sheet22.ComboBox1.List
On Error Resume Next
For Each i In ThisWorkbook.Sheets
i.ComboBox1.Clear
i.ComboBox1.List = ListCombo
Next
End Sub
Now, I can't find a way to activate the sheet specify in the combobox. Could someone give me any piece of advise?
Thank you and best regards.
Alberto
|

January 1st, 2016, 03:18 PM
|
Wrox Author
|
|
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
|
|
Hello Alberto, thank you for buying the book.
See if the example and downloadable workbook from my blog helps you to see how to have a combobox on your Ribbon as an add-in from which you can select a worksheet. The concept is the same for whatever combobox you've listed the worksheets in.
http://www.atlaspm.com/toms-tutorial...rop-down-list/
|
The Following User Says Thank You to Tom Urtis For This Useful Post:
|
|

January 4th, 2016, 02:02 PM
|
Registered User
|
|
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
I appreciated you help Tom. Although I tried to do the exact same thing as your example but I want to add a combobox (Useform style) in one Excel worksheet. Could you help me with coding?
Best regards.
|

January 4th, 2016, 02:34 PM
|
Wrox Author
|
|
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
|
|
Can you explain more about your project, what you are working with, and what your expected results are. Especially, please explain exactly where the combobox is -- you characterized it as a userform style, and then in one worksheet. Trying to understand what your project looks like and what it does.
|

January 4th, 2016, 03:54 PM
|
Registered User
|
|
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
I am basically trying to install a combobox on a random worksheet (for this projet is Sheet22) with a list of equipment in which the worksheets are identify with the name of such equipment. I would like that the user choose an equipment from the combobox and depending on selection, the macro will sum the cost of all accessories require and return the value in the sheet where the combobox is located on a random cell (Sheet22).
For now, I was trying to activate the sheet depending on combobox selection. Your initial macro worked perfectly but I would like the combobox on the worksheet and not in the commandbutton bar.
I hope my explanation is clear enough. Really sorry for the language quality, English is not my mother tongue.
Regards.
|

January 4th, 2016, 11:18 PM
|
Wrox Author
|
|
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
|
|
What is the name of the combobox? You can select the combobox on the sheet in Design mode and seeing its name in the Name Box.
And just to be clear, you want to select a worksheet whose tab name is exactly the same as the item in the combobox that was selected.
By the way, to do what you said you want to do (sum the cost of all accessories and stick that sum number in a particular cell) without actually selecting the worksheet of interest.
|

January 5th, 2016, 09:33 AM
|
Registered User
|
|
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
What is the name of the combobox? ComboBox1 You can select the combobox on the sheet in Design mode and seeing its name in the Name Box.
And just to be clear, you want to select a worksheet whose tab name is exactly the same as the item in the combobox that was selected. Yes
By the way, to do what you said you want to do (sum the cost of all accessories and stick that sum number in a particular cell) without actually selecting the worksheet of interest. Is there a more efficient way to do it? What is your suggestion?
Thank you
|

January 5th, 2016, 11:36 PM
|
Wrox Author
|
|
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
|
|
The code to do what you said you want, which is to activate the worksheet whose name is in the ActiveX ComboBox named ComboBox1, is as follows.
To install it in your workbook, first, copy this procedure onto your clipboard:
Private Sub ComboBox1_Change()
With ComboBox1
Worksheets(.List(.ListIndex)).Activate
End With
End Sub
Then, go to your workbook.
Right-click on the worksheet tab that holds the ComboBox.
From that tab's pop-up menu, select View Code.
You will be taken to the module for that worksheet.
Paste the above procedure into that worksheet module.
Press Alt+Q to return to your worksheet.
Now, when you are not in Design mode, when you select a worksheet name in that ComboBox, you will automatically activate the worksheet of the same name.
Regarding a more efficient way to accomplish your task, here's what you wrote:
"user choose an equipment from the combobox and depending on selection, the macro will sum the cost of all accessories require and return the value in the sheet where the combobox is located on a random cell"
The moment you select the worksheet name from the ComboBox, you can reference the parent sheet name of the range holding the numbers to be programmatically summed on that sheet. Then you can programmatically place that sum in the random cell you spoke of.
|

January 6th, 2016, 09:42 AM
|
Registered User
|
|
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Thank you very much Tom.
It works perfectly. I really appreciate your help.
Best regards.
Alberto
|
|
 |
|