Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 30th, 2015, 10:42 AM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #2 (permalink)  
Old January 1st, 2016, 02:18 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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/
Reply With Quote
The Following User Says Thank You to Tom Urtis For This Useful Post:
albertoc (January 4th, 2016)
  #3 (permalink)  
Old January 4th, 2016, 01:02 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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.
Reply With Quote
  #4 (permalink)  
Old January 4th, 2016, 01:34 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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.
Reply With Quote
  #5 (permalink)  
Old January 4th, 2016, 02:54 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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.
Reply With Quote
  #6 (permalink)  
Old January 4th, 2016, 10:18 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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.
Reply With Quote
  #7 (permalink)  
Old January 5th, 2016, 08:33 AM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #8 (permalink)  
Old January 5th, 2016, 10:36 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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.
Reply With Quote
  #9 (permalink)  
Old January 6th, 2016, 08:42 AM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 09: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 08:44 AM



All times are GMT -4. The time now is 04:34 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.