Creating a command button in excel

[Liam]

Hi,
I am wanting to create a command button in excel. For example, I have a drop down list (data validation) in in cell A1, and then in cell A2 I want to put a button in that, when clicked, will run a macro that takes the user to, say, sheet2, and depending on what entry they select in the drop down list, for the cursor to go to the corresponding column on sheet2.

I know that a hlookup can be used, but in my s/sheet there are a few added complexities. There are actually 3 drop down lists, one giving the option of monthly / quarterly data (macro hopefully taking you to the corresponding tab), one for the name described above taking you to the corresponding column, and a 3rd list with dates which will hopefully take you to the correct corresponding row. Once all 3 have been selected, I wanted a "Go to" button that when clicked for the cursor to take you exactly where you want. However, I am not that good on VBA and was wondering if anyone could provide any help??

Quite a long request, and any help steering me in the right direction would be greatly appreciated!!

Thanks,
Liam

 

[Andrew Poulsom]

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?

 

[Liam]

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

 

[Andrew Poulsom]

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:

Private Sub CommandButton1_Click()
    Dim WS As Worksheet
    Dim c As Integer
    Dim r As Long
    Set WS = Worksheets(Range("A1").Value)
    c = WorksheetFunction.Match(Range("B1").Value, WS.Rows(1), 0)
    r = WorksheetFunction.Match(Range("C1").Value, WS.Columns(c), 0)
    WS.Activate
    ActiveSheet.Cells(r, c).Select
End Sub

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.

 

[Liam]

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

 

[Andrew Poulsom]

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:

r = WorksheetFunction.Match(Range("C1").Value, WS.Columns(c), 0)

to:

r = WorksheetFunction.Match(Range("C1").Value, WS.Columns(1), 0)

 

[Liam]

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

 

[Andrew Poulsom]

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?

 

[Liam]

It comes back with true.

 

[Liam]

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