Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Run CommandButton Code From Another Macro

I'm trying to loop through several objects and fire their "Click" events, but I'm not sure how to get it to work.

Below is an example of what I was trying to do. There are 3 objects with the name "CommandButton(1-3)".

Private Sub RunAll_Click()
Dim obj As Object
For i = 1 To 3
Set obj = UserForm1.Controls("CommandButton" & i)
Next i
End Sub

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro To Add CommandButton & Assign Macro Code
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 Replies!   View Related
Creating CommandButton With Code
how to create commandbutton code so let me explain what I am trying to do and anyone that could provide me with some basic code details to solve my problem:

When the command button is clicked in an excel worksheet I want a pop up box to come up containing specific information sourced from two other excel worksheets depending on the commandbutton. What I want to know is what code do I enter from the visual basic view in order for the box to appear with the right information.

View Replies!   View Related
Create CommandButton With Code & Position It On Sheet
How can I create a commandbutton with code & position it.

View Replies!   View Related
Determine If Worksheet CommandButton Fired Event Code
When I click on a commandbutton20 on sheet1 it moves the user from sheet1 to sheet2, hides some columns & formats the height of a row in a range of cells I will refer to as Area X. On sheet 2 I have a target condition that if the user clicks on any part of Area X it move the cursor to cell C8. What I want to accomplish is that if commandbutton20 in clicked, the target condition is suspended. otherwise the target condition works. The code I wrote is as follows. What happens when I press F5 to execute it... a macro window pops up.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a1:x3,e4:x6")) Is Nothing Then
If Worksheets(1).CommandButton20_Click() = True Then
Exit Sub
Else: Range("c8").Select
End If
End If
End Sub

View Replies!   View Related
Excecute A Macro From A Commandbutton
I have a little problem with a userform commandbutton that I cannot resolve. I cannot seem to excecute a macro from a commandbutton, but if I go to the VBA project I can get it to work from there.

The cmb just calls a macro, which in itself call 5 other macros. This is the code for the button:

View Replies!   View Related
Assign Macro To Commandbutton
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 Replies!   View Related
Macro Debugs When Run Via CommandButton
I have an unusual problem with my excel workbook. The workbook consists of 2 sheets, "WorksOrder" and "Data". In the WorksOrder sheet, the user enters some WorksOrder number and then hits a Commandbutton to retrieve the data from SQL and stores it in the "Data" sheet. It then goes and copies the values from the Data sheet. The problem occurs when it's trying to copy a cell value in Data into a WorksOrder cell. The error I get is Run-time error '1004': Application-defined or object-defined error

The weird thing is, when I'm stepping through the code, I don't get the error message and the code works. It only happens when the code is triggered from the Commandbutton. I've tracked the error to a variable which supposed to have a row number greater than zero but instead has a zero (0) value. My question is why isn't the function returning the value to the variable ThisRow? The code for copying the cell data is listed below:

Dim ThisRow As Long
Dim ThisWorksOrder As String
ThisWorksOrder = "123456"
ThisRow = FindRowNumber(ThisWorksOrder)
Worksheets("WorksOrder").Range("D1").Value = WorkSheets("Data").Range("H" & CStr(ThisRow).Value.............

View Replies!   View Related
Getting A Commandbutton Macro To Do Operations On An Adjacent Worksheet
I am trying to make things easier for the next user by having one command button run the macros for the other worksheets (so they don't have to switch between worksheet)

I get "Runtime error '1004' Application-defined or object-defined error"

View Replies!   View Related
CommandButton On WorkSheet
I have a commandbutton (CommandButton1) on a worksheet ("SCHEDULE"). I was wondering if there's a way to freeze the commandbutton on the bottom, on the right... I would like that, when you scroll down or to the right, the commandbutton doesn't move....

My code is:

Private Sub CommandButton1_Click()
Dim ScrollRw As Long 'Botón De comando parte inferior izquierda
Dim ScrollCol As Integer 'de la hoja "PROGRAMACION"
ScrollRw = ActiveWindow.ScrollRow
ScrollCol = ActiveWindow.ScrollColumn
Modulo1.BorrarTabla 'Código que borra la tabla

View Replies!   View Related
Use String To Set As Commandbutton
I have a large number of commandbuttons on a userform that I wish to apply captions to via a for loop. Each button is numbered appropriately for this purpose. e.g.

Button_1, Button_2 etc etc

I want the loop to compile a string that represents the button name e.g.

String = "Button_" & Loop_Count
and then use my defined commandbutton:

Dim My_Button as commandbutton
to name the buttons. So the loop would be something like:

for Loop_Count = 1 to 100

String = "Userform_Name.Button_" & Loop_Count
set My_Button = String
My_Button.caption = "Test_" & Loop_Count

next Loop_Count
Unfortunately trying to set 'My_Button' using a string like this doesn't work.

View Replies!   View Related
Create New Worksheet From Commandbutton
I have a userform which collects fuel data from a pump and populates 2 worksheets (pump1) and (pump2). What I am tyring to accomplish is the following:

When fuelling is finished for the day, the operator selects the (Shutdown) button. When this happens I need the userform to perform the following.

1. For each worksheet (pump1) and (pump2) create new worksheets named (pump1_date) and (pump2_date).

2. Populate these new worksheets with the entire data from the original worksheets.

3. Clear out all but the last row of data from the original sheets. The last row contains the meter readings for the fuel pumps and needs to stay as an opening ballance for the next day. This last row to become the top row of the original sheet.

View Replies!   View Related
CommandButton And FIND Fails
Haven't done a LOT with forms, but this should work (in my mind) and doesn't.

I'm using a ComboBox linked to a named range of "DATES" for a user to select a date. All I want to do is find that date in column A of the Sheet1.

View Replies!   View Related
CommandButton, Text Box, Calculator
I am trying to make a calculator inside of Excel...learning how Text Boxes work in conjunction with CommandButton. I am trying to code the button to display inside of the text box. I have never coded a text box before to do anything like that

note: For right now i am just looking at being able to click on a button [numbered 0-9] and have them dsplayed inside the text box. After that I want to be able to set up an addition, subtraction, etc button to actually have the math done.

View Replies!   View Related
Force 'Save As' Through CommandButton
I have created a tool in excel to facilitate different requests from our sales force. A drop down list in the first worksheet lists the request types. Once the type is selected the user clicks a command button that sends the user to the corresponding worksheet specific to the request type.

My problem is that I need to verify that the cells on the specific worksheet have all been completed. I tried to verify when saving in the workbook module, but I don't know how to only check the selected request worksheet.

One thought was to insert save command buttons on each worksheet and only allow saving through those buttons. (So I would need to remove any saving functionality from the file menu/toolbar.) Then I could attach the verification code to that button on each worksheet. Preventing saving until the requested data was entered.

If this methodology will work I need to know how to do the following:

1) remove alternative saving methods

2) Once those methods are removed, how do I code a command button to check that specific cells are not null and then "save as".

3) if cells are null then, stop save and show msgbox.

I am working in Excel 2003.

View Replies!   View Related
Link A CommandButton To Another File
linking a CommandButton to another File. I made a CommandButton but would now like to know how to write the Macro to make that Button, when clicked, bring me to a file saved in Excel.

View Replies!   View Related
Commandbutton Error '287' When Cancelled
I've have a button to open a file on my first sheet When I click on the button a window pops up to open or cancel the action. There are no problems when I click OK to open the file. but when I cancel I get a runtime Error '287' Application-difined error.

View Replies!   View Related
CommandButton To Clear Textboxes In Vba
Can I use a commandButton too clear data out of my textBoxes. Been reading a lot and do not understand how,I'm the type of guy if you show me too the water I will drink it. I will send a example of what I want too do.

View Replies!   View Related
Anchor-Fix CommandButton To A Cell
Is there any way to bind a Commandbutton to a cell's location, so that when column widths or row heights are shifted, that the CommandButton moves with the cell?

View Replies!   View Related
Change Commandbutton Color
I have a workbook with multiple sheets and a different number of command buttons per sheet. How can I create a loop to color these command buttons? I have been successful at changing the size of the command buttons but not the color. The commandbutton is grouped with a chart.

Dim grp As Shape
stp = ThisWorkbook.Sheets.Count
strt = stp - 2
For a = strt To stp
b = 0
For Each grp In Sheets(a).Shapes
Selection.ShapeRange.GroupItems(6).Top = 100 'works
Selection.ShapeRange.GroupItems(6).BackColor = vbBlack 'doesn't work
Next grp
Next a

View Replies!   View Related
VBA Macro Code Needed To Run The Code Contained Within A Cell
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.

Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"

I need a macro to "execute this code", as if it were in the macro itself.

I have assigned the above to a variable, but am not sure how to execute it.


Dim the_calc
the_calc = Range("B5").value

Now, how do I run the_calc ?

View Replies!   View Related
CommandButton Generate Number When Pressed
I want to get number (going from 1 to 15 (for example)) when i click on commandbutton (on userform without textboxes, combos , etc, ..... except only one commandbutton)!

In other words, when i click on commandbutton, i want to get (on msgbox) number 1, then when i click again on commandbutton, i want to get number 2, then when i click on it third time, i want to get number three on msgbox window, and so on until i close userform!

View Replies!   View Related
Commandbutton To Enter Cell Info
Usually you press the enter key or any of the arrow key to enter cell information. Is is possible to press a commandbutton to enter the cell information and perform some operations without pressing the enterbutton first?


View Replies!   View Related
Add Somekind Of Search Feature To Commandbutton(s)
How can I add somekind of (auto)search feature to a CommandButton?
So when I click CommandButtonA I get transfered to the first keyword (content) in Column A starting with the letter: A (or: a)

Private Sub CommandButtonA_Click()

' When you click on CommandButtonA goto firts keyword in Column A starting with A (or: a)
' When you click on CommandButtonB goto firts keyword in Column A starting with B (or: b)
' etc, etc, etc.

End Sub

View Replies!   View Related
MSForms.CommandButton Not Working Properly
I have a form with 2 buttons and a frame. Inside the frame I have another button. I get a Type Error Message when I run the code and I don't understand why.

View Replies!   View Related
CommandButton Caption Text Clipped
I have a commandbutton who's caption text is getting clipped along the bottom when I view the sheet at 100% zoom. If I look at 200%, the text is fine. What's going on here? I've included a pic of the offending button. Note the missing bottom part of the "g".

View Replies!   View Related
CommandButton Linked To Locked Cell
I have created a simple excel document with

Sub randomnumbers()
Range("B1").Formula = "=randbetween(1,100)"
Range("B1").Value = Range("B1").Value
End Sub

There is a button that i click and it creates a random number to cell 'B1'. it works fine, but i need to lock the document so that the user can only access one cell... 'A1' when i lock the document, and i click the button, it gives me a '400' error. i think it is because the button points to a locked cell.

View Replies!   View Related
Color UserForm CommandButton When Clicked
is there ant way to clearly highlight command buttons on a userform that the program user selected (clicked on) ie change its clour or raise it etc so the user can easly see which button they have clicked on.

View Replies!   View Related
Hand Cursor For Control Commandbutton
I'm using commandbutton in a worksheet, and would like it to have the same cursor displayed as the 'forms' control.... for some reason, it remains an 'arrow' when the forms becomes a hand. I've identified the 'mousepointer' property, as well as the 'mouseicon' properties; however, it appears that for some reason, I don't have the hand-cursor icon on my system. (i've searched the hard disk, and apparently this is not entirely uncommon according to some posts I've found around the internet).

So my question/favor to ask is:
1) where can i find the standard windows xp system scheme "link select" hand-cursor
(control panel->mouse->mouse pointers... last one)

2) I believe its filename is "hand-l.cur"..... if anyone reading this post can quickly check their windowscursors directory for this file, and reply/ upload it

View Replies!   View Related
Textbox Enter Key To Force Execute Commandbutton
In my userform, I have a textbox where I enter a number.

When I hit the return key in the textbox, I want the userform to act like I hit a commandbutton_Click. (The commandbutton_Click takes the contents of the textbox and locates a line with that number in it in a specific worksheet, and then displays the line of info in other textboxes in the userform).

I have not been able to figure this out.

View Replies!   View Related
How Text Boxes Work In Conjunction With CommandButton
I am trying to make a calculator inside of Excel...learning how Text Boxes work in conjunction with CommandButton. I am trying to code the button to display inside of the text box. I have never coded a text box before to do anything like.

note: For right now i am just looking at being able to click on a button [numbered 0-9] and have them dsplayed inside the text box. After that I want to be able to set up an addition, subtraction, etc button to actually have the math done.
(ie...if Button 1 is clicked, 1 will be displayed, if Button 2 is clicked the display will adjust to show 12, etc etc)

View Replies!   View Related
Enable CommandButton When UserForm TextBox Has X Characters
I have a command button that I set to be disabled but I want it to become enabled as soon as 3 characters are entered into a textbox.

View Replies!   View Related
CommandButton Font Size Increases On Each Click
I am experiencing exactly the same problem as described in Textbox Text Increases In Size With Click here, so browse to the following location to view the sample file:


username "turbocam"
password "danielsresort"

View Replies!   View Related
Enter Key To Trigger CommandButton Click Event
I am working with a large legacy file/program which has a lot of issues. Foremost, and unfortunately this cannot be changed, is that all of the controls were placed directly on the worksheets instead of on Userforms. The actual code from the legacy file is excessively complicated, so I created this simple example. Sheet1 has one textbox and one command button. Sheet 2 is blank. I want to be able to click the command button or use the Enter key on the command button, to trigger the Click Event. Clicking works fine. When using Enter, if Sheet2.Activate is commented, everything works fine. If Sheet2.Activate is executed, then Excel crashes.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
'Only look for tab, return, down arrow, up arrow
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application. ScreenUpdating = False
'Do we need to go back to previous control
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
If bBackwards Then TextBox1.Activate Else CommandButton1.Activate
Application.ScreenUpdating = True
End Select
End Sub
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim bBackwards, bForwards As Boolean
Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False...................

View Replies!   View Related
Efficient VBA Code Vs Macro Recorder Code
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.

1) Macro Recorder Code to Copy One Sheet to Another

Sheets("Data Apple").Select

2) Macro Recorder Code for Replacement Purposes......................

View Replies!   View Related
Intercept & Cancel Keystroke Events When CommandButton Is Active
I'm building an Excel worksheet into a kind of dashboard with CommandButtons and TextBoxes to drive the functionality of the rest of the document.

I'm trying to recreate some of the functionality of a UserForm (e.g., tab order). I'm using the CommandButton's KeyDown event to respond if the user presses Enter:

View Replies!   View Related
Delete Currently Displayed Combobox Item With Commandbutton Click
I have a combobox which is manually scrolled through using the down arrow key. As the different combobox items are viewed, I would like to be able to delete the currently viewed item from the list if desired by clicking on a commandbutton.

The list items are contained in the range of B2:B500. Also, since this places a gap in the list, I would like to shift the remaining cells values in column B up to close the gap.

View Replies!   View Related
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)

The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...

The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).

The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.

'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23

'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub

View Replies!   View Related
Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View Replies!   View Related
Macro To Delete Vba Code - Macro Security Warning
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View Replies!   View Related
Deactivate Or "grey-out" Commandbutton
I was wondering if there is any way to "grey-out" a commandbutton? I made it using the forms toolbar.

View Replies!   View Related
Macro: Find The Code
i need a macro that will look in column B and find the following rec codes, if found then keep these codes else delete (Entire row) for all other rec codes in this column,


View Replies!   View Related
Adding Code Into A Macro
I've added the following code into macro i have assigned to the button on this attached worksheet which deletes any row where the cell in the A column is blank.

View Replies!   View Related
Sort Code In Macro
I have a sort function in one of my macro. Sometimes it works, sometimes it does not. I can't figure out why. I am trying to sort columns A-F and each column has a header.

View Replies!   View Related
Code To Search For A Macro
way to code in Excel 2000 a macro that would on "workbook open" search for a specific macro and if its there run it?

View Replies!   View Related
How To Code A Down Keystroke In Macro
this it the row. Article No. is A1, but G04532 is A10073 because of sorting. I want to select A1 and do a for loop downwards to see if the next cell is the same. Ultimately i want to copy A1:A7 because they are the same....

View Replies!   View Related
Macro For Checkboxes For Fix My Code
The following code attempt is in a userform with one print button, one cancel button and six checkboxes refering to which sheets that are needed to print.

View Replies!   View Related
VBA Code Which Deletes A Macro
I’ve got a macro that automatically starts when the workbook is open. The macro finishes with


The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty).
Does anyone know if there exists a code to delete a macro?

View Replies!   View Related
AutoShape Name That Ran Macro Code
I have a large map of a region with which I want to create autoshapes to allow certain areas to be zoomed into. So far i have made an autoshape, assigned a macro, and used

ActiveWindow.Zoom = True

To zoom into it. At least that is the theory. What I have discovered is that this only works when a shape is selected, which does not occur when you click on it. Is there a way to get the name of the autoshape being clicked, so that i may select it within the macro and zoom in on it. Or is there a better way of doing this? I am trying to avoid making explicit macros for each autoshape as I will have many of them by the time this is complete.

View Replies!   View Related
Sum Range In Macro Code
I am writing some code to help speed up data input into Excel. I take the info from the user through a form. Before pasting the data into a spreadsheet, I need to check that there is no data there already.

How can I check that the sum of range of cells in excel, as specified by the user in the form, is Zero?

I want to do this in the code rather than enter another cell in excel and sum it there

View Replies!   View Related
Vlookup In Macro Code
convert this formula to VBA.

The are four formulas in there:

1. lookup formula.

2. remove the 1st number (exmaple: remove 0 in 0123 to become 123)

3. add a number (example: add 0 to 123 to become 0123)

4. convert a number into text format.

View Replies!   View Related
Copyright © 2005-08, All rights reserved