Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer
|
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
 
Old December 30th, 2015, 11:42 AM
Registered User
 
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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
 
Old January 1st, 2016, 03:18 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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:
albertoc (January 4th, 2016)
 
Old January 4th, 2016, 02:02 PM
Registered User
 
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
 
Old January 4th, 2016, 02:34 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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.
 
Old January 4th, 2016, 03:54 PM
Registered User
 
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
 
Old January 4th, 2016, 11:18 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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.
 
Old January 5th, 2016, 09:33 AM
Registered User
 
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
 
Old January 5th, 2016, 11:36 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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.
 
Old January 6th, 2016, 09:42 AM
Registered User
 
Join Date: Dec 2015
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thank you very much Tom.

It works perfectly. I really appreciate your help.

Best regards.

Alberto





Similar Threads
Thread Thread Starter Forum Replies Last Post
Defining a sheet from a combobox to determine which sheet the info is added to thmsjlmnt Excel VBA 1 June 21st, 2013 09:48 PM
How to position to a specific sheet before saving pkipe Excel VBA 1 December 17th, 2007 10:36 PM
activate sheet mike0123m Excel VBA 1 August 11th, 2007 01:19 PM
Copy specific data from one sheet to another yogeshyl Excel VBA 2 May 11th, 2007 09:14 AM
Activate Outlook and open a specific mailfolder Mats Excel VBA 0 January 24th, 2006 09:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.