Creating a command button in excel

First, a bit more information:

1. What's in you first drop down and which sheet do you want to activate for each item?

2. What's in the second drop down and what is the relationship between each item and the column to be used?

Well, the first drop down will be a drop down of 2 things:
(1) Monthly
(2) Quarterly

If you select monthly, then I want the "Monthly" sheet to be active (or active after you have selected the other 2 variables)

The 2nd drop down is a long list of fund types, such as "UK Equity Core", "UK Equity Core - Pooled" etc. But for simplicity we can call these A, B.... Z. If "C" is chosen, the I want the cursor to go along row 1 until it reaches a cell that says "C" (Note, it won't be the 3rd column).

The third part is the date which is in each of the 2 sheets (monthly & quarterly) in column A, so when you select, for example, 30/06/1997, then if it could search down cloumn A of the correct sheet.

So in summary, 1st on selects the worksheet to search, the 2nd provides the column by looking up row 1, and the 3rd selects the row by looking up column A. Once the button is pressed, the cursor should then go to the correct cell on the correct sheet.

Is this possible at all? I thought it would be straightforward but quickly realised it was quite involved and way beyond my programming abilities!

Hope this makes sense.

Liam

Right click any ToolBar and choose Control ToolBox. Click the CommandButton and click and drag in your worksheet to position and size it. Right click the button and choose View Code. Paste this code in the window on the right:


Note that the first and last lines will already be there. The code assumes that your data validation cells are A1, B1 and C1 - change the code to suit.

Press Alt+F11 to return to your workbook. Click the Design icon (top left) in the Control Toolbox to exit design mode, and close the ToolBox.

Make some selections and click the button.

That looks quite good, but for some reason when I click the button nothing happens. I'm thinking does this have anything to do with it searching in column(c)? The dates only appear in column A of each sheet, and the products only in row 1. Do I need to amend the column it's searching for the dates, or is it something a bit more involved?

Sorry for being such a pain!!

Liam

I'm a bit surprised that nothing happens - you did exit design mode didn't you?

To look only in column A for the date change:


to:

Yeah, I think the columns(1) works fine, but the only problem with it is that it is having trouble searching for the dates. I think it's a date formatting thing, because when I replace the dates with general numbers the search works fine and it jumps to the correct place on the correct sheet. However when I replace the dates the debugger runs and the date match line is the one that is being highlighted.

Thanks for all your help setting this up, however if anyone knows how I could alter the line
r = WorksheetFunction.Match(Range("C1").Value, WS.Columns(1), 0)

so it can successfully search for dates, then I would be greatly appreciative!

Thanks again!
Liam

I think it's more a "Text v Number" thing than a formatting thing. If you apply the ISNUMBER function to cell C1 what result do you get? And the same for one of the dates on your other sheets?

It comes back with true.

Sorry, and it comes back with true for the other sheets as well.