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]

I am not sure why it is not working in that case. Maybe try:

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

 

[Liam]

Andrew,
That works perfectly now that I put that new line of code in. Thankyou so much for all your help! My spreadsheet is so much easier to use now.

Just out of curiosity if you have time, what does CLng do?

Many, many thanks!

Liam

 

[Andrew Poulsom]

It converts the date into a Long Integer (eg Today is 37970). VBA sometimes has problems with UK style dates when matching. This forces it to use the underlying numerical values.

 

[Liam]

Aaah, makes sense. Again Andrew, thanks for figuring that out for me!

Kind Regards,
Liam

 

[a.0501772607]

Dear sir,
acctuly i want to creat a Command Button that i click on it and a figer will appear in desire cell,
for exp, click on command button and a figer "105" will appear in "B10"...
thnx

 

[sujon]

now its work!

 

[emogal]

Hi andrew,

can i have a question here: For example, I have selected "April" in the drop down list and the 'X' cross mark is on the cell at E14 by using the formula. so my question is: is that can create a button to let user click on to see the description that based on the 'X' in the cell?