Free Microsoft Excel 2013 Quick Reference

Run Macro from Command Button

Hi

Nice simple one thats driving me mad !

I have created a macro in an Excel worksheet that I want to be able to
run by clicking a command button on the same worksheet.I just cant
seem to be able to find how to do it.

I have created the new button on the sheet by the way.

The macro is called PrintMe.

Thanks v.much in advance

Steve


I am having trouble running a macro that I got off this site. It just protect/unprotects all the sheets at once. I want to just have a cmd button that runs it instead of have to go up to the macrolist. People will be using this that have no idea what a macro is. I get an error that reads "Run-time error '1004': Method 'Protect' of object '_Worksheet' failed". If I just run the macro from the list, no error. I have the cmd button call the module and then the module shows the userform. When I debug it takes me to either of the lines within the if else statement, depending on if the sheet is already protected or not. Here is my code, and any help would be much appreciated.


	VB:
	
 cmdPass_Click() 
    Call ShowPass 
End Sub 
 
Sub ShowPass() 
    UserForm1.Show 
End Sub 
 
Private Sub cmdOK_Click() 
    Dim wSheet As Worksheet 
    For Each wSheet In Worksheets 
        If wSheet.ProtectContents = True Then 
            wSheet.Unprotect Password:=TextBox1.Text 
        Else 
            wSheet.Protect Password:=TextBox1.Text 
        End If 
    Next wSheet 
    Unload Me 
End Sub 
 
Private Sub UserForm_Click() 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks

I want to run a macro when I open a particular spreadsheet. I know I
can get a macro to run every single time it opens, but I do not want
that. I want to start Excel externally, (from a shortcut) and either
load it and run the macro, or load it and not run the macro.

I do not see any option to open and run a macro from the command line.

Does it exist? Is there another way to pull this off?

Phil

Is there any way to run a macro from the command line while opening excel?
ie: c:xxxxxxxx.xls "macroname"
?

if not, basically I am trying to write a program that reads all of the files in a directory, and for all of the excel files, it opens them, runs a specific macro on them, and then saves and closes them.

Does anyone have any ideas?

Help please . . .

I have a macro that I've assigned to a button on a worksheet. I've
protected the VBA code and I only want the users to be able to run the
macro from my button.

How do I disable the user from running the macro from Tools > Macro?

TIA

Steve

I have the following macro established and it works when the active sheet is the one I want to run the macro in. When I try to add it to a button on another sheet, it no longer works. The debugger points to row 31. What do I need to change to be able to run this macro from a button on another sheet?

Sub Delete_Duplicates()

'DELETE DUPLICATES
With Sheets("DATA")
Dim LLoop As Integer
    Dim LTestLoop As Integer

    Dim Lrows As Integer
    Dim LRange As String

    Dim LCnt As Integer

    'Column values
    Dim LColA_1, LColI_1, LColM_1, LColP_1, LColS_1, LColU_1 As String
    Dim LColA_2, LColI_2, LColM_2, LColP_2, LColS_2, LColU_2 As String

    'Test first 2000 rows in spreadsheet for duplicates (delete any duplicates found as well
    ' as the original row)
    Lrows = 2000
    LLoop = 2

    'First pass: Check first 2000 rows in spreadsheet (only flag records for deletion)
    While LLoop <= Lrows
        LColA_1 = "A" & CStr(LLoop)
        LColI_1 = "I" & CStr(LLoop)
        LColM_1 = "M" & CStr(LLoop)
        LColP_1 = "P" & CStr(LLoop)
        LColS_1 = "S" & CStr(LLoop)
        LColU_1 = "U" & CStr(LLoop)

        If Len(Range(LColA_1).Value) > 0 Then

            'Test each value for uniqueness
            LTestLoop = LLoop + 1
            While LTestLoop <= Lrows
                If LLoop <> LTestLoop Then
                    LColA_2 = "A" & CStr(LTestLoop)
                    LColI_2 = "I" & CStr(LTestLoop)
                    LColM_2 = "M" & CStr(LTestLoop)
                    LColP_2 = "P" & CStr(LTestLoop)
                    LColS_2 = "S" & CStr(LTestLoop)
                    LColU_2 = "U" & CStr(LTestLoop)

                    'Value has been duplicated in another cell (based on values in columns A to H)
                    If (Range(LColA_1).Value = Range(LColA_2).Value) _
                     And (Range(LColI_1).Value = Range(LColI_2).Value) _
                     And (Range(LColM_1).Value = Range(LColM_2).Value) _
                     And (Range(LColP_1).Value = Range(LColP_2).Value) _
                     And (Range(LColS_1).Value = Range(LColS_2).Value) _
                     And (Range(LColU_1).Value = Range(LColU_2).Value) Then

                        'Flag the duplicate and original for deletion
                        Range(LColI_2).Value = "DELETE"

                    End If
                End If

                LTestLoop = LTestLoop + 1
            Wend

        End If

        LLoop = LLoop + 1
    Wend

    LCnt = 0
    LLoop = 2

    'Second pass: Delete records flagged for deletion
    While LLoop <= Lrows
        If Range("I" & CStr(LLoop)) = "DELETE" Then

            'Delete row
            Rows(CStr(LLoop) & ":" & CStr(LLoop)).Select
            Selection.Delete Shift:=xlUp

            'Decrement counter since row was deleted
            LLoop = LLoop - 1

            LCnt = LCnt + 1

        End If

        LLoop = LLoop + 1
    Wend

    'Show how many rows deleted
    
    MsgBox CStr(LCnt) & " rows have been deleted."
End With

End Sub


Short version:
i would like to know if it's possible to run macros from external source (ie: another workbook or exported file).

Long version:
i have created a series of 4 workbooks to store figures for different aspects my place of work. All these work fine but the company i work for has 40 sites, each site requires a complete set of these 4 workbooks, totalling 160 workbooks. I was thinking that alot of the size of these workbooks are just repeated forms and sub routines and if i could get them all to run forms and sub routines from one central location the space saved would be massive.

I hope this makes sense, any ideas ?

I have a macro that works fine when I run it a shortcut ket, but it when I run the same code from a command button it returns the error:

runtime error 1004:
selec method of Range class failed

Does anyone know why this could happen?

I can't write macros, so have to create them by actually recording the process. I did that just now and then copied it into the Command button. When I run it from Tools>Macros, it works, but when I run it using the Command button it fails.

I'm trying to copy and paste values current MTD figures (P18:P39) to previous MTD area (W18:W39), but the command button fails at this selection. Cells 28-35 are blank, but I don't think this makes any difference, as I've tried the command button with those cells zeroed out.

This is what I recorded initially; this works from Tools>Macros

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 04/01/2008 by Lucertola
'
'
Sheets("Reconciliation").Select
Range("P18:P39").Select
Selection.Copy
Range("W18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
End Sub

I copied the above into the Command button (on another tab, so far only called Sheet1)and got this, which fails (it highlights the Range("P18:P39").Select line):

Private Sub CommandButton1_Click()

' Macro recorded 04/01/2008 by Lucertola
'

Sheets("Reconciliation").Select
Range("P18:P39").Select
Selection.Copy
Range("W18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
End Sub

What am I doing wrong?

I have created macros that I would like to run when a command button (placed
on the excel worksheet) is clicked. How can I do this?

I have created macros that I would like to run when a command button (placed
on the excel worksheet) is clicked. How can I do this?

Hi there,

I have a excel documents that carry out a number of routines and I am looking to build an all-in-one controlling document.

basically it will access each of these excel documents and run the macros within, then close before moving into the next one. I do however need a hand with the following sections within each macro...

1) VB code that will select "Okay" to a message box
2) VB code that will select "Yes" to a vbYesNo message box
3) VB code that will populate a userform text box then "click" a command button
4) select a variable from a combobox

...each of these macro's are built to be run by a user and I want to automate the human intervention part.

many thanks,

Andy

Hi,

I am currently trying to make my first VBA enhanced spreadsheet. It is basically a spreadsheet that uses 2 lookup tables to find values for use in the building construction industry.

My question is this:

I want to have multiple sheets with information on them and a front page that gives options for each sheet (the options are in the form of buttons that the user simply clicks to do as they wish). One of these options will be to print that specific sheet (each sheet has a row of buttons). This is where I seem to run into problems. Without having a macro for each specific sheet how can I use the button to print out the sheet I want.

The current print macro code is as follows:

Sub print_to_pdf( shtname As String)
Sheets(shtname).Select
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Options").Select
End Sub

From my limited knowledge it appears that you cant pass information to a macro from a button press (i.e. print_to_pdf("Sheet1")) you can only pass a 'flat' macro name (i.e. print_to_pdf).

Is there anyway that I can get the button to A. Pass information to a macro Or B. Have the button return its position when it is toggled (if I knew the positon of which button was pressed then I can work back from that to find out which sheet is needed to be printed).

Any other ways round this problem would be greatly appreciated.

I am using windows Xp and excel 2003

Thanks in advance,
Matt.

Hi and thanks for the help. I am using Excel 2003.

I accidently used the wrong button and am hoping there is a fix. I need to
run a macro from a button.

I know that if I use the button (control button) from the forms toolbar the
first thing it asks me to do is to assign a macro and all would work fine.
But...

I used the command button from the control toolbox to make the buttons (I
made many of them and would prefer not to remake them). I would like to run a
macro from each of these buttons and now I can't figure out how to attach the
macro to the buttons I've already made. The buttons came from the command
button on the control toolbox.

Is there a way to attach a macro to these buttons?? THANKS !

I posted this question earlier today but I forgot to mention an important
item. The right click, assign macro choice is not available with the button I
used (the command button on the control toolbox.) Here's the earlier post.

I accidently used the wrong button and am hoping there is a fix. I need to
run a macro from a button.

I know that if I use the button (control button) from the forms toolbar the
first thing it asks me to do is to assign a macro and all would work fine.
But...

I used the command button from the control toolbox to make the buttons (I
made many of them and would prefer not to remake them). I would like to run a
macro from each of these buttons and now I can't figure out how to attach the
macro to the buttons I've already made. The buttons came from the command
button on the control toolbox.

Is there a way to attach a macro to these buttons?? THANKS !

I can open a tab-separated file automatically from the command line, eg process_create("excel.exe","file.txt"), but then I want to Save As an Excel file: I can create a macro to do that, but how can I run the macro automatically, on the command line?
Any help appreciated!

Is there a way to run an Excel macro from a command line. Such as when you
open up a file to have it run the macro. Since I want to do this for files
that are newly created I can not program the file to run the macro at
startup. Is there a command line option in excel?

Hello all,

Can someone please help? I am very new to vba but I have managed to write code (multiple subs strung together with Call statements) for about 25 sheets in this one workbook. Each sheet has a command button that initiates the data maipulation that I want to accomplish on that particular sheet. It all works very well when I go to each sheet and click on the command button.

Now I want to make all of these processes run from one command button on the first sheet. All of the subs on every sheet now have unique names because I anticipated wanting to string them all together and run them from one command button. I guess you call this a module for the entire workbook, but I am still struggling with the terminalogy of all this programming, so don't know for sure.

I added the code from the second sheet/command button to the end of the code from the first sheet/command button and joined them with a Call statement. Figured I would go about this sistematically, sheet by sheet. Instantly, I got an error message " Activate method of range class failed" when I ran it. At the start of the added code, I had added something like Worksheets("Sheet2").Activate figuring that I needed to activate that worksheet in order to make the rest of the code work. That is where I got the error.

Sorry for the long winded explanation. Can someone tell me what I need to add/do in order for the code to run without error as I string together all the pieces from each sheet?

I just got upgrade from Excel '97 to '2000. I used to be able to assign a macro from right click on a command button. Option isn't there now. How do I assign a macro to my command button?

Hi people,

Is there a way of running excel macro from command line or using .bat ( batch file or operating system script file ).

I want to write a batch file which converts text file into excel file with certain columns protected and store it in new file name.

Can it be done from dos/windows/unix command prompt without invoking MS-Excel by double clicking?

Thanks in advance,

-N

Hey, everyone--

I'm trying to run a macro from a dialog sheet-- is that possible?

Basically, I have a dialog form that includes a disclaimer that automatically launches when the file opens. I have 2 buttons on the dialog. The accept button unhides the very hidden worksheets and the user can operate the model because he/she has accepted the disclaimer.

The do not accept macro would automatically close the workbook.

Is this the way to do it? Any better suggestions? I also need to know how to get around the fact that many users may have security settings that automatically disable macros which would prevent any of the code from running.

Any help would be greatly appreciated.

Thanks,
Codias

Hi,

I have created macro, assigned it to button on quick access toolbar. When i save workbook with different name and then when I pushing this button on quick access toolbar, original workbook opens and macro is starting up from it, not from current workbook with different name. But when I use hot-keys to run this macro, it is starting from active workbook (as I want).

What I'm thinking is, that when I assign macro to the button, it only assigns to only that certain macro. But how to assign that like "public" not "private" ?

Sorry for my pure english, but hope you understand what I mean

I need a macro for a command button to perform the following:

If B8 is the active cell and I click the command button,
transfer value from:

B3 to B8
D3 to D8
E3 to E8
F3 to F8
G3 to G8
H3 to H8
I3 to I8

Likewise, if B9 is the active cell and I click the command button,
transfer value from:

B3 to B9
D3 to D9
E3 to E9
F3 to F9
G3 to G9
H3 to H9
I3 to I9

Note:

The source cells will always be B3, D3, E3, F3, G3, H3, I3
The data in the source cells (drop list) change. And as they change, I'd like
to transfer the new data by using a command button to transfer the data to the active cell.

The first row is 5
The last row is 4833

Cannot use range as Col B to Col I because Col C data is locked, hidden and cannot be included.

Also:
If it is not to confusing, I would much rather use a toggle button instead of the command box to clear (make blank) B3, D3, E3, F3, G3, H3, I3 when I'm finished with each transfer.

Hi all. I have about 200 command buttons on my spreadsheet. Since there are so many of these, I would like to create a VBA routine to determine the cell name four cells over from the cell each command button sits inside, if possible (each command button is small and sits within its own cell). So the command button in cell F1, will let me pull the data or get the cell name of cell C2. So I guess I am trying to figure out how to do relative references from a command button. Then this will be put in a public variable and passed to a subroutine which is the same for each command button. Only the data in the cells relative to the command button changes. I thought activecell might be useful for this, but you when press a command button, it doesn't make that cell the active cell.

I just thought there might be a simple solution for this, instead of having each single command button have its own subroutine just so each one can reference cells. All I really need is to run the same subroutine with variables from 200 cells, one at a time. Sorry if my explanation is poor.. Thanks!

Please help, What is the command to run a macro from an IF condition. The
macro is called MacroB so: IF(K39=60,run macro,stop) from the programming
format of: if cell K39 =60 the run the macro else stop. Thank you