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

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/

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.

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.

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.

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.

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

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.

Thank you very much Tom.

It works perfectly. I really appreciate your help.

Best regards.

Alberto