Help with macros

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
I am just starting to experiment with macros i can get the macro to work and assign a shortcut key and tollbar button, however i have seen worksheets with action buttons in which when hit performs the macro, how do i put one of these in a worksheet.????????????
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
There are two ways:

The easiest is using a Forms button. When drawn you'll be asked to assign a macro.

The second is to use an ActiveX control from the Control Toolbox, which requires you to code each control individually, but you can call general subs from them.

I.E.

Private Sub CommandButton1_Click()
Call Macro1
End Sub

Hope that helps,

Smitty
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
I am going to sound really thick here that was great got that sorted and working but want to take it a step further ie i can get my macro to run commands but what is the best way to incorperate formulas ie if macro1 is run then A1 = ??? eg when macro run A1=B1+C1, i have tried doing it but it seems to run the formulas before i run the macro.

Basically i want a macro that only calculates my info when promted.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post the code you have now and indicate what you're trying to do?

You can always set Calculation to manual at the beginning of your code and set it to automatic at the end if that's what you're after.

Smitty
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934

ADVERTISEMENT

I want to set up 4 macros which perform different tasks with the same data. ie:

Macro1

Looks in cell A1 then searches rows B1 to AA1 for a match to A1 then in the corresponding column counts the total number of "H".

The other macros are going to be the same but searching different columns so i will just adaot the first macro.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
What's "H"?

If it's text that you're searching for in that range wouldn't COUNTIF work?

Smitty
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934

ADVERTISEMENT

Yes ican do it with a countif however im experimenting with macros that use formulas so just want to use a simple formula in a macro with an action button so i can get the hang of them.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
There are two ways to do that. Actually record the formula; get a formula working the way you want, then record a macro and hit F2-->Enter on that cell to record it.

Or you can use Applicaiton.WorksheetFunction. The helpfile details it pretty well.

Smitty
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
I have a sheet with various data in colums i want a macro that will do three things,

eg
columnA columnB ColumnC

Paul 17:00 H
John 21:00 w
Steve 14:00 S
Ian 05:00 W

I want to reset the sheet so all cells with a "W" in column C are at the top then sort these cells be column A in alphabetical order then sort by column B in time order.

Does this make sense?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Why not just record it:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro1()
    Range("A1:C5").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range( _
        "A2"), Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

That'll give you the base code.

Smitty
 

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top