Free Microsoft Excel 2013 Quick Reference

Determine which userform command button was clicked (VBA)

Hello.

I have had no luck searching for a possible answer to my question.

In Excel 2003, I have a UserForm which contains several command buttons.

When the form is displayed and a button clicked, I want to use VBA to determine which button was clicked.

Is it possible to do this? If so, how would one do it?


I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet name
on the menu to go to a given sheet . However, I am finding
it difficult to assign a single macro to OnAction property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal

I have a user form in Excel that is filled at run time with variable number of CommandButtons named after values in spreadsheet.

So you end up with variable number of buttons named for example "Germany, England, Scotland etc.".

That works fine via Me.Controls.Add method!

NOW, I WANT TO KNOW WHICH BUTTON WAS CLICKED. HOW DO I DO THAT?

Thanks!

I am developing a workbook that manages menubars and toolbars so I can easily add macros to shortcut menus, delete buttons on tool bars etc.

As part of this spreadhseet, i would like to be able to easily assign a particular icon to a button on a toolbar. I have created a temporary toolbar that has 100 buttons with random faces. The buttons are not associated with an OnAction event (yet). What I want to be able to do, is click a button and capture which button was clicked. Once I know which button was clicked, I will know which button to assign to the main toolbar I am working with.

I had 2 ideas, neither which I know how to get to work.

1. Assign every button 1 - 100 to the same macro, and try to capture the name or number of the button that was just clicked. I am not aware of an event that will allow me to do this however.
2. Build a custom macro at the time that I add the button to the toolbar. ie if I add button 24, I call it button24, then create a macro called button24Click() that passes number 24 back to my other code. Trouble is I don't know how to get VBA to create VBA code.

If anyone can help with how to do either of these, or has any other ideas, I would love to hear from you.

Matt

Hi Guys,

I was hoping you might be able to advise me how to change the colour of VBA Userform Command Buttons.

This may be the .backcolour feature...not sure...could you please also advise how to find a reference guide to the colour index ie what number refers to each colour.

Much Appreciated.

Michael.

I have a two command buttons on a multipage userform:

The "OK" button transfers the user's data from the userform to a spreadsheet successfully, but does not clear the form.

The "Enter Additional CAS #" returns the user to the first page of the userform, but also does not clear the previous information.

Can someone please provide me with code that will clear the form once these command buttons are clicked?

Regards,
NB

Good Afternoon,

Could someone please help me. What I am trying to accomplish is to have a msgbox show the date the last time a commandbutton was clicked (click event). Could someone please help me with this.

Thanks PJ

Hi

I am new to this list, but would appreciate any help available.

I have userrform with several clusters of checkboxes and for each
cluster a command button that allows the user to select or deselect
all the check boxes in a cluster, using code typically as follows:

Private Sub cmdSelAllStages_Click()

' select / deselect all stages, if any stage is false make them
all true,
' else make all stages false

If chkPPM0 = False Or chkPPM1 = False Or chkPPM2 = False Or
chkPPM3 = False Then
chkPPM0 = True
chkPPM1 = True
chkPPM2 = True
chkPPM3 = True
Else
chkPPM0 = False
chkPPM1 = False
chkPPM2 = False
chkPPM3 = False
End If

. . . . . . . .

End Sub

Each checkbox has a worksheet cell set as its 'controlsource' property
to store its current value.

Problem is that when the userform is displayed and one, some or all of
the cluster of checkboxes is false then, when the command button is
clicked as the first action, the code only makes the first checkbox in
the cluster true (or the first checkbox in the cluster that is false,
true) instead of, as I want, making all the checkboxes true if any of
them are false.

The reverse is also true - if all boxes are true when command button
is clicked it makes only the first box untrue, rather than making all
of them untrue.

The command buttons work fine on any subsequent occassion when the
command button is clicked - it is just when it is the first action on
using the userform that the problem occurs!

Can anyone explain this or provide a fix for it?

Any help much appreciated, its got me baffled.

Mike

I have a button that labeled "Employee Signature" that when pressed, uses an image from another part of the worksheet and places it in the signature box (it is a scan of the employee's signature). The code is as follows:

Sub rebeccawilsonsignature()
    With ActiveSheet.Shapes("rebeccawilsonsignature").Duplicate
        .Top = Range("AF66").Top
        .Left = Range("AF66").Left
    End With
End Sub

Is there a way to add to the VBA for this button so that it automatically time stamps cell AK69 with the date and time that the button was clicked; formatted as: 12/21/10 14:01

Thanks again in advance for any assistance with this!

Hi,

Thanks for looking at my post..!

Quick question..

If I need to clear data that is on another tab in a worksheet, and I want to clear this from a UserForm Command Button ("CLEAR"), what is the code for the Clear button to do this?

Thank you very much!

Hello--I am looking for a tidy way to end a main procedure from a
userform command button. One idea that might work (say the newsgroups)
is to to run a boolean function procedure that returns a value, False
if certain conditions are met. Then one could check for a false value
in the main procedure before continuing. However, I am having trouble
connecting userform buttons with function rather than sub procedures. I
am sure that this is a common problem. Can anyone suggest an elegant
way out? Thanks. -Abe

Hello,

Ok, I have a userform, with a grouped set of 5 radio buttons.
Is their a fast way of determining which of the 5 was selected, instead of
having to do a _click determination of each radio selection itself.

I have the the groupname to : Score1_RadioGroup

Also, is their a way to indicate the choice of the group through arrow key
selection?

Currently, I have a textbox where a number will be entered, and then I
would like the user to be able to tab to the group of radio buttons, and using
an arrow key, to set the desired choice.
The reason, is that it would allow for all key press commands to set all the
options without having to use the mouse. Allows for much faster data entry.

Thanks in advance,

spyrule.

This is moreso a VBA userform question that I use through Excel. When I open my excel file it loads my userform automatically. When I click on any of the buttons in the "Save CSV Files" frame......I have a macro that runs and performs a certain task.

After the macro completes I programmed the background color of the button to change green indicating that the macro has been run for that day. I want the buttons to remain green until I reach Saturday PM (which will actually be pushed on Satuday in the PM) in which I need to insert code to change all the buttons back to their normal background color of grey (indicating a new week is about to start).

Currently the code I am using to change the background color to green is:

MondayCustomCSVAMButton.BackColor = 52582

The problem is when I press "Exit" it doesn't change any of the cells that I changed to green by clicking on them.

Problem #1 - I can't figure out what string of code I need to use in my "exit" command button which will save the altered background colors of the buttons I pushed as green.

Problem #2 - Once I figure out how to solve problem #1 then I need to figure out a string of code that will change all cells I changed to green during the week back to the default color of gray when the Saturday PM button is pushed.

Any help is appreciated. Screen shot is attached.

I would greatly appreciate any help with this, Im sure some of you will see my error right away.

I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet.

Here is where my problem is coming in

The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"

I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. ie
before load figures
after load figures

and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet.

So if someone selects "loadopen" command button I want the following code to run on my worksheets.

==========================================================

	VB:
	
) 
    cancel = True 
     
    oneportvolume = Target.Value 
    Sheet11.Range("D10") = oneportvolume 
    Sheet11.Range("G10") = Target.Offset(0, 1).Value 
    MsgBox (Target.Value) 
     
End Sub 

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

and if they select the "loadclose" command button I want this code to run

	VB:
	
) 
    cancel = True 
     
    oneportvolume = Target.Value 
    Sheet11.Range("D27") = oneportvolume 
    Sheet11.Range("G27") = Target.Offset(0, 1).Value 
    MsgBox (Target.Value) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
==========================================================
now the only thing that has changed are the cells into which the number will be placed.

So how do I accomplish this, "i've tried IF then statements without success" I probably just did not set it up correctly

My other question is about the variable "oneportvolume" do I need to change the variable also to prevent the code from overwriting the previously entered number?

Sorry if this was too long or too much info. If needed I will try to shorten the questions

Thanks so much to anyone willing to help me

I'm trying ot use following line of code...

ActiveSheet.Shapes(Application.Caller).TopLeftCell .Row

....to generically determine the row in which a button was clicked (which
initiates a macro). The code works great for Buttons created with the Forms
toolbar, but does not work for Command Buttons created with the Controls
Toolbox toolbar (which, I assume is ActiveX). I need to use the latter type
so I can also programatically change its BackColor.

The error seems to be Error 2023.

Any way around this?

I assign the same macro to all my ToggleButtons, and use Application.Caller to determine which button was clicked.
Sub Click_Button()
    Dim s As String
    Dim bnButton As ToggleButton

    On Error Resume Next
    s = Application.Caller
    On Error GoTo HE

    If s <> "" Then
        On Error Resume Next
        Set bnButton = ActiveSheet.buttons(s)
        On Error GoTo HE

        If Not bnButton Is Nothing Then
            s = "'" & ActiveWorkbook.Name & "'!" & "Button_Subs"
            If Not Run(s, bnButton) Then GoTo EF
        End If
    End If
HE:
    'Error Handler
EF:
End Sub
I also do the same thing for CheckBoxes, but using this code
         On Error Resume Next
         Set ckCheckBox = ActiveSheet.CheckBoxes(s)
         On Error GoTo HE
I would like to do the same thing for OptionButtons, but I can't figure out what to put here:
        On Error Resume Next
        Set obOption = ActiveSheet.????(s)
        On Error GoTo HE
"s" has the name of the OptionButon in it, but I can't get it into an object.
"OptionButtons(s)" doesn't work and neither does "Shapes(s)".
How can I get the OptionButton object?

I know that I could probably cycle through all the shapes on the worksheet looking for the one with that name, but I would rather go directly.

Is there a way to programmatically determine (i.e., using
VBA code) which rows and/or columns are outlined on an
Excel spreadsheet?

I have spreadsheets with three different groups of columns
outlined (one level of outline demotion each - no
overlap). The specific columns that are outlined change
based on the data. I would like to write VBA code to 1)
programmatically determine the groups of outlined columns,
and then, 2) reduce the number of outlined columns in one
of the groups (the third one).

Example using R1C1 reference notation: Outlined
columns: 1, 5-12, and 19-40

My goal is to reduce the third outline group by the first
three columns of the group. In this case group 19-40
would have columns 19-21 promoted by using code like
Range(Cells(1, 19), Cells(1, 21)).Select
Selection.Columns.Ungroup

It is easy to promote the columns manually, but it is a
pain to do over and over again at the end of an otherwise
automated process (VBA code) that is formatting the
spreadsheet.

For the curious - why do I want to promote three columns
programmatically? VBA code is used to extensively format
an Excel file including adding lookup table data as new
worksheets, adding formulas that use the VLOOKUP function,
a macro button and code "behind" the button to provide
sort options, borders, outlining to hide some cost history
columns and future projections columns so a window of data
around the current month data is displayed and the report
is constrained to one print page wide on 11" wide by 17"
high tabloid size paper at about 64% reduction, etc. The
data includes hours and dollars totals for labor,
material, travel, etc. A "values only" summary of the
totals rows is created as a separate (11" wide by 8-1/2"
high when printed) "Summary" sheet. Additional
calculations are added to the summary sheet
programmatically. Some columns at the right side of the
data are not needed on the summary sheet. These columns
are deleted. This leaves the summary sheet "viewable
print data" not as wide as the area available. Removing
three columns from the hidden outline of the future months
fills the page and shows more data.

The kind of outlining I am referring to has to do with how
to show or hide detail data (i.e., rows and/or columns) in
an outline. If you enter this phrase, "Show or hide
detail data in an outline", in the Help Answer Wizard you
will see how outlining is done manually.

I want to programmatically determine which columns
are "outlined" (i.e., can be shown by a click on a "+"
button in the margin of the spreadsheet outside the grid
area or hidden easily by a click on a "-" button in the
margin of the spreadsheet outside the grid area. I assume
there is a collection where this data is stored (something
like columns.outline or some such).

Thanks for any help.

I'm trying ot use following line of code...

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

....to generically determine the row in which a button was clicked (which
initiates a macro). The code works great for Buttons created with the Forms
toolbar, but does not work for Command Buttons created with the Controls
Toolbox toolbar (which, I assume is ActiveX). I need to use the latter type
so I can also programatically change its BackColor.

The error seems to be Error 2023.

Any way around this?

Is it possible to modify the caption of a command button on a userform based on a condition that exists prior to the userform being fired?

For example, if cell A1 is 100 then the userform would display a button with the caption "Hi". If it was 200 then "Bye".

the caommand button would have its own code for each condition, that if it was 100 it would turn A2 to blue and if it was 200 Red.

thanks in Advance.

PLEASE
How do I make an image function as a command button? I mean instead of creating a command button to click, I'd like the user to click the image and make the image function as cmdButton clicked?

I tried overlapping a cell with a transparent image or label but when the image or label was clicked, the image or label became visible which results to overlapping the cell.
Thank you very much!

Is there a built-in function in the custom/ generic installation of Excel [i mean in the normal-usual installation] to get the end of month, i.e Aug 31, 2003 for the month of Aug, Sept 30, 2003 for the month of Sept. If none, how can I get the end of month? I tried the for loop of [28 - 31] and test the date if it is valid,

ex.
'Month is Feb
Private Function Get_End_Day(MyMonth As String, MyYear As String) As String
Dim ctr As Integer
Dim stemp As String

For ctr = 28 To 31
If IsDate(DateSerial(MyYear, MyMonth, ctr)) Then stemp = Format(DateSerial(MyYear, MyMonth, ctr), "MMMM DD")
Next
Get_End_Day = stemp
End Function

My purpose for the function is to return 28 as the month's end date. But instead, the loop continues and return March 2.

Thanks in advance.

Dear Friends,
I am in need of help. I'm need of a code that do the following:
- from the first book excel userform, filling in the textbox and When I click in Command button:
1º Open a second workbook excel (specific) and put that workbook hidden;
2º generate a number (ID) and automatically copy the information from hidden textbox to the book.
3º Save and close the second workbook

or
if more could be too easy:
- from the first workbook excel userform, filling in the textbox and When I click in Commandbutton:
1º copy of the information of the textbox to the page of the first book;
2º open a second workbook is hidden so;
3º automatically generate code and copy the information from the first workbook for the second
4º Save and close the second workbook

Thank you for your availability
This code is important because I make a file to improve my job and I need to finish in next week!!!

Best regards
Pedro Ramoa

Dear Friends,
I am in need of help. I'm need of a code that do the following:
- from the first book excel userform, filling in the textbox and When I click in Command button:
1º Open a second workbook excel (specific) and put that workbook hidden;
2º generate a number (ID) and automatically copy the information from hidden textbox to the book.
3º Save and close the second workbook

or
if more could be too easy:
- from the first workbook excel userform, filling in the textbox and When I click in Commandbutton:
1º copy of the information of the textbox to the page of the first book;
2º open a second workbook is hidden so;
3º automatically generate code and copy the information from the first workbook for the second
4º Save and close the second workbook

Thank you for your availability
This code is an urgent because I make a file to improve my job and I need to finish in next week!!!

Best regards
Pedro Ramoa

There was a similar question I found answered in the archives. However the solution given there (using ActiveSheet.Buttons(Application.Caller).TopLeftCell.xxx) does not work in my case. A little googling and I think the reason is that I added my buttons from the toolbar and not from the forms editor. My buttons are in the OLEObjects collection, not in Buttons.

So how do I determine which of my OLEObjects was clicked? Is there something similar to "Application.Caller"?

Hi there - i am using excel 2003.
I have created a userform with Checkboxes and command buttons.

Is it possible to do the following:

On command button "OK" Click - check to see that all checkboxes have been ticked before proceeding.

If all checkboxes have not been ticked - display message "Please tick your Checkboxes before proceeding".

There are 3 checkboxes in question.

Look forward to anyones response on this.
THank you.

Hi there.
I have a tried searching on the net but am just getting confused with all the results. I have an autoform and would like to know (if someone doesnt mind helping) how to change the following:

Change background colors (i am using 2003 btw) and cant seem any property on userform to change Style to Graphical (which i have read on the net that you should do).

ALSO - i am trying to color command buttons too or, rather, change the foreground or background colors to enhance visual appearance of my userform.

Hope someone can help me with this please.
Thank you in advance.