While practicing writing macros and studying the VBA code, I decided to add a button to run the macro, something I've done many times in the past. But the option to associate the new button with any macro doesn't seem to be available.
A. How can I assign a new button to a macro
and
B. How can I make the association in VBA?
I have to modify 2000 files so I've written a master file that loops through a list, importing modules and forms into the 2000 files it opens and closes. On each file when open it draws a button on worksheet. I then want to assign a macro to the button called "ModifyMenu" . The code for 'Modifymenu' is imported into file_01.xls and is a unique name. It does not exist is master.xls, so no confusion.
The code for assigning the code is as follows :
Windows("file_01.xls").Activate
ActiveSheet.Shapes("Button").Select
or
Workbooks("file_01.xls").Sheets("1 B").Shapes("Button").Select
Then
Selection.OnAction = "ModifyMenu"
(This links to master.xls!ModifyMenu not file_01.xls)
Selection.OnAction = "file_01.xls!ModifyMenu"
(This fails, runtime 1004. 'Unable to set the OnAction property of the button class)
Selection.OnAction = ThisWorkbook.Name & "!ModifyMenu"
(This links to master. MAster is the active code even though 'file_01.xls' is the active worksheet)
Variations on the above seem to result in the same 1004 error or linking back to master regardless of the fact I'm trying to link to file_01.xls.
I have a start page in the beginning of my excel workbook.
This is where i want to assign a macro to a search button.
I want the macro to search the entire workbook but only in a certain block on each worksheet.
I want to assign a macro of start time and end time on one button. I tried but its not working..
View 2 Replies View RelatedCreated a button in a spreadsheet but cannot activate the shortcut menu that contains "assign macro." Help says select the button, right click on a handle and a shortcut menu appears with "assign macro." Not. I right clicked every handle numerous times and get the regular shortcut menu without the "assign macro" option.
View 9 Replies View Relatedhow I can write code to create a button on a worksheets and then link to another subprocedure.
I tried recording a macro and this is what excel so eloquently gave me:
ActiveSheet.Buttons.Add(384.75, 60.75, 79.5, 39.75).Select
Selection.OnAction = "CreateImport"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Parse Deposits for Import"
With Selection.Characters(Start:=1, Length:=25).Font
. Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
This works fine, my only problem is if the button that is created is actually named Button2 or 3, etc. Is there a way to assign the new button to a variable, or would it be an object? this way it wouldn't matter what it was named.
I'm trying to assign a shortcut to a macro I wrote in VB. However, when I go to Tools->Macros->Macros, none of my macros (whether coded in VB or recorded) have the "Options..." button enabled, so I can't assign the macro. This happens whether I use Excel 2003 or Excel 2007. The file is not readonly and I have tried the various levels of macro security. I have VBA installed.
View 5 Replies View RelatedIs there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?
how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"
I've been trying to figure out ways to edit the VBA in "Module 1" to make this work
Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start.
Code:
Sub LoanData()
'
' LoanData Macro
[Code].....
How can I execute two macros with one button press?
the sub names are CDO_Text and CDO_Mail
I need to assign two actions to a one button.
1. action: Copy Column A, paste to Column C
2. Write time&date into G5
I am only capable of doing one button for each action using the macro recorder and simple code:
[Code] .....
I have had the following written for me
Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Target
If Cell.Column = 4 and Cell.row >= 17 and Cell.row <= 20 Then
If Cell <>"" Then
Cell.Offset(0, 3) = Date
Else
Cell.Offset(0, 3) = ""
End If
End If
Next Cell
End Sub
I wish to assign that code to a button on sheet 1.
I normally just right click the buitton and assign the macro, but on this occasion I can't. I think it's to do with it being a private sub.
I am creating controls on a Userform on the fly. A shortened example of my code to create a button is below:
Set BtnEdit = .Controls.Add("Forms.CommandButton.1", "BtnEdit", True)
With BtnEdit
.Left = 220
.Top = 85
.Width = 50
.Font.Underline = True
.Caption = "Edit"
.Enabled = True
End With
What I want to do is assign a pre-existing sub-routine to the button, at the same time as I create it. I have tried using
.OnAction = Mysubroutine
Userform2 is popped up from another userform3.
How can I have userform 3 when clicked fill in a textbox on userform2 and then finally assign it to cell F3 on my worksheet.
VB:
Private Sub CommandButton2_Click()End Sub
So I need commandbutton2 (which needs to retain a value of 1` when clicked ) on Userform3 to assign the value of 1 to TextBox44 (Which is using control Source F3 so the value here ends up in F3
VB:
Private Sub TextBox44_Change()
If TextBox44 > 5 Then
MsgBox "Your entry must be part between 1 and 5", vbCritical
Exit Sub
End If
End Sub
Sheet 2 contains a drop down list containing names, would like to assign a Macro to a button on same sheet that would refer to sheet 3 based on certain criteria.
Sheet 3 contains columns A - K of data. 2 criteria, data in column D and column K. Column D would be the same as names in the drop down list, whereas column K refers to a constant, either y or n.
Example: If D contains "Cheddar" and K contains "Y", copy and paste A:K to Sheet1, preferably starting at row 7.
I written VBA code to create a new sheet and embed a command button on it. I want to assign a macro to this button but when I click on the button the assign macro button is greyed out. I have plenty of macros saved and the VBA code has finished running so why should this be?
View 7 Replies View RelatedContext: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
[Code] .....
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"
ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit
i have the following code for sending a worksheet to a closed workbook
View 14 Replies View RelatedI would like a to activate a form every time I click in a given cell. how to do this?
View 4 Replies View RelatedI have several macros where I have assigned keyboard shortcuts, as below. Yet they don't work.
Sub My_FastCleanup()
' My_FastCleanup Macro
' Keyboard Shortcut: Ctrl+Shift+M
-macro stuff-
End Sub
From the Control Toolbox I click on the command button icon. I positon the mouse where I'd like to place the command button and draw the command button. Then right click to open the drop down menu. A few days ago in the drop down menu I had an option Assign Macro but now I don't have it anymore. How is it possible to assign macros to the command button and even better how can I get the Assign Macro option back in the drop down menu?
View 2 Replies View RelatedI have designed a functioning userform which transfers the data (limited or complete) into the sheet. Along with the data and at the end of each row it automatically generates 4 checkboxes and links them to 4 offset cells.
This is where I hit a dead end. I am not sure which avenue is suitable or indeed possible. I want to click any checkboxes in the first COLUMN and as a result have it change the complete ROW colorindex of the background. I did achieve this with conditional formatting but it seemed to make the empty sheet bulking when saving and I cannot alter the font size which is another goal on the 3rd column of checkboxes. Am I waffling yet?
The 3rd column checkboxes I want to have the ROW font size drop to 3pt. The 2nd and 4th currently serve no purpose i.e. trigger nothing as a result.
I don’t think I can use the checkbox_click event as the boxes are generated for me so I don’t know which number they will be. The worksheet_change event and calculate event don’t seem to register when the linked cell goes to TRUE or back to FALSE. I tried using FORMS and CONTROLS checkboxes but neither has brought me success.
I was hoping the linkedcell would trigger an event and in turn select and manipulate the entire row in which the linked cell is located….
Due to my limited knowledge the code I have written/adapted to suit my needs may appear a little unorthodox? I hope that in itself is not a problem.
I have not managed to get the Controls Checkboxes to initalise blank. Currently they appear greyed out with a tick but fully usable? Of minor importance.
I can't attach the file because the blank sheet is 4.5MB?? so here is the main body of code from the userform. If you need anything else I will supply as required, however I can't get anyfiles under the attachment maximum of 48.8kb. My jpeg image was 108kb....
Context: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
VB:
[COLOR=#333333]Dim Btn As Button[/COLOR]
[COLOR=#333333]Dim rng As Range[/COLOR]
[COLOR=#333333]For I = 2 To RowCount + 1[/COLOR]
[COLOR=#333333]With Worksheets("Sheet1")[/COLOR]
[COLOR=#333333]Set rng = .Range("B" & I)[/COLOR]
[COLOR=#333333]Set Btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)[/COLOR]
[Code]....
The following code is my Zip macro:
VB:
[COLOR=#333333]Sub Zip()[/COLOR]
[COLOR=#333333]Dim strDate As String, SavePath As String, sFName As String[/COLOR]
[COLOR=#333333]Dim oApp As Object, iCtr As Long, I As Integer[/COLOR]
[COLOR=#333333]Dim vArr, FileNameZip[/COLOR]
[COLOR=#333333]Dim FName() As Variant[/COLOR]
[Code]...
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
My macro essentially requires a deletion of the entire sheet, so I cannot run it through a button, it'd by definition be deleted. So I was thinking to run this macro through the keyboard, how to assign a keyboard key to existing macro?
View 4 Replies View Relatedi want to know how to assign a macro which will remove and place a number in a certain cell. so if i click and tick the box, then a certain figure will come into a cell(this will be a formulae) and when untick the the number will disappear.
View 4 Replies View RelatedI have several Activex buttons in sheet "Options" and I would like to automatically assign each button to the same macro "Run_Options", in order to execute the macro "Run_Options" when I do click over any button.
How can I do that with a excel macro?
User inputs cost in a cell then clicks macro button that will run a macro that ask for a task description. The user inputs the task info that relates to the cost (i.e. weld pipe). This data is stored on a seperate sheet in the corresponding cell (cost is on sheet "hours" and task description is on sheet "task description" both in same cell. This allows a copy of the cost sheet to reflect the task description. I have tried recording macros but can't get anywhere.
View 1 Replies View RelatedI created a drop down list (of various Input Groups) and I need to show the selected group's responsibilities on a separate sheet; is there an easy way to do this?
View 2 Replies View Related