Call Userform From Variable Number Of ActiveX Command Buttons
Jun 27, 2014
I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form
View 14 Replies
ADVERTISEMENT
Jan 7, 2010
how to get some command buttons to stick in one place? I currently have two in a summary sheet. Each button runs a similar code which clears and deletes (by shifting cells upward) at the start of the process, then populates data.
It's rather annoying though that I cant have the buttons stick to one zone, they end up moving left or right depending on which button I press. Each report also changes column sizes, dont know if that's relevant?
View 3 Replies
View Related
Feb 22, 2008
On a worksheet, I have several commandbuttons. For many of the buttons the name in the "name box" is different than the name in the "(name)" field under Properties.
Researching this, I found this on msdn2.microsoft.com:
An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.
So, in my case, one button has:
In the name box: "CommandButton5"
In the (name) properties field: "RunProcess"
The real problem arises when I'm using code to loop through the objects on the worksheet, and build an array of the object names. I'm trying to match the name of the buttons to their click events. In the code, "btn.name" returns the name found in the "name box". The one that I want is the name in the "(name)" field under properties because this is the one that matches the click event.
How can I determine, in code, what is entered in the "(name)" field for an object?
View 5 Replies
View Related
Feb 15, 2010
In the attached workbook there is a UserForm with five (5) command buttons along the bottom of the UserForm. I'm familiar with making them the same size and aligning tops. What I'm struggling with is how to distribute them horizontally. That is, to get the same amount of "white space" between them.
View 9 Replies
View Related
Feb 15, 2010
I've fought with this issue off and on for some time now and decided it was time to come to you for help. In the attached workbook there is a UserForm with five (5) command buttons along the bottom of the UserForm. I'm familiar with making them the same size and aligning tops. What I'm struggling with is how to distribute them horizontally. That is, to get the same amount of "white space" between them.
View 8 Replies
View Related
Jun 23, 2008
I have recently obtained some help on code to bring up a user form based on cell change selection.
the code is as follows:
Dim c As Range
If Not Intersect(Target, Range("C6")) Is Nothing Then
With Sheets("Data Set").Range("D4:D43")
Set c = .Find(Range("C6").Value, LookIn:=xlValues)
If Not c Is Nothing Then
UserForm4.Show
End If
End With
End If
It brings up userform4, but the command buttons do not carry out the code behind them:
Private Sub CommandButton2_Click()
Sheets("Data Entry Form").Range("$C$6") = "Choose Employee"
Unload Me
End Sub
If I run user form seperate from the sheet and bring it up only in visual basic, it works...Any ideas???
PS this command button is no so it resets field C6. The other button is yes and performs copy paste function. Neither are operational when user form is brought up through the sheet change. Only works when initialized in VB.
View 9 Replies
View Related
May 1, 2014
I have created a userform on VBA which our clients will use. There are a number of different userforms which have different questions to make up the tool. At the end of each field (text box) there is a submit button (command button) which needs to be pressed. When the user presses next to move onto the next userform I need a control in place that ensures all of the 'submit' buttons have been pressed. If not i want something to say, 'you have not pressed all the submit buttons'. At the minute I have a simple reminder message box before they move onto the next screen but i need this control to be tightned.
View 1 Replies
View Related
Sep 4, 2008
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
View 9 Replies
View Related
Mar 14, 2007
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
View 9 Replies
View Related
Mar 19, 2014
I am looking for a way to use 2 optionbuttons in vba. The option buttons are in different groups, and are independent.
I cannot get this to work. Is this even possible? In sheet1, the vba would read something like this.
View 9 Replies
View Related
Feb 24, 2013
I 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?
View 2 Replies
View Related
Jun 10, 2014
I want to write a VBA code that will place ActiveX Frame directly onto the worksheet and then add 3 Option Buttons from Toolbox into that frame.
I could not find an answer searching through the web... Also when trying to record macro and see the code it did not record placing the buttons from the toolbox or any formatting (color, caption, etc.) via properties...
When formatting the buttons it would be useful if the selected radio button would have different color, bold font, etc.
View 5 Replies
View Related
Dec 23, 2013
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
View 2 Replies
View Related
Aug 12, 2009
I have created an ActiveX command button and the text on the button looks crude. I know that is not very descriptive but I am unsure how to describe it. The text looks jagged and some letters are missing chunks of their letters. The font I have chosen is Calibri.
If I create a Form Controls button I do not have this problem but I want the button to have a particular background color so I do not believe I can use a Form Controls button.
I do not know why the other Calibri text on the worksheet looks fine while the ActiveX Calibri text does not. The attached image shows the two buttons with the top one being the ActiveX button while the bottom is a Forms button.
View 11 Replies
View Related
Aug 26, 2013
I have made an excel workbook detailing team performance that all of my team have access to and that requires updating once a day. The first person to access the workbook each day presses a Command Button that updates the workbook and inserts a time/date stamp. However, other members of the team go into the workbook and update it again the same day which then affects the data being reported.
I know i should probably just sack them for not being able to read the time and date but HR would not like that so is there a way to disable CommandButton1 (ActiveX) so that once it is pressed it is disables until the next day?
View 3 Replies
View Related
May 10, 2009
I've searched through the forums for this information, and I have found a couple of similar issues that have been solved, but copy+pasting the code into my form has not had favorable results.
Basically, here is what I am after:
I have a sheet that already has 4 buttons active, with a handful of other subs that they are calling. I am wanting to be able to add new buttons and have them call a universal macro that will forward them to my existing subs. However, I'm wanting a variable to be set based on the Caption of the button that was clicked. For example:
View 14 Replies
View Related
Dec 30, 2009
I designed my spreadsheet incorrect.
Everything works good. But when I selecting tables with listbox, I forgot to notice that there also comboboxes.
I mean, rwgrietveld, I use selecting table to hide 'em, but when I hide 'em comboxes should also disappear (become invisible).
is it possible at all to make invisible activex commandbutton in selection?
View 11 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Jun 15, 2014
well, the clear worked well and now I have another idea for a button for my worksheet;
I want the button to insert values from other cells. so when it is clicked, the values in cells F82-F86 are entered in cells E19 - E23.
View 5 Replies
View Related
Apr 22, 2006
I have created a command button so that the sheet asks for a password. Now I need to include that in an already created code. I have created a command button as:
Private Sub CommandButton1_Click()
If LCase(Me.TextBox2) = "password" Then
Run "Showall"
Else
Run "Hideall"
End If
End Sub
Can someone tell me how to call this macro in another macro. That is when another macro is running and if the condition fails, this macro should be called. Is it something like run "CommandButton1"?
View 9 Replies
View Related
Feb 6, 2014
I have a workbook with two worksheets, "Main" and "Control".
Both of them are with hidden tabs (unchecked "show sheet tabs").
On worksheet "Main" I have command button which opens worksheet "Control".
The assigned makro is: Sheets("Control").Select
The problem is: when I open "Control" and close after that the workbook, the next time when I open the workbook "Control" pops-up instead of "Main" even though I do not save it.
1. I need macro on "Main" which will allow me to open "Control".
2. Regardless I "Save" or "Don't Save" "Control" when selected and workbook is closed, to open the workbook always displaying worksheet "Main".
I use Excel 2007 (at home) & 2010 (at office).
View 1 Replies
View Related
May 22, 2007
I have a form using text boxes to enter some data into a spreadsheet. These text boxes appear in pairs for any single point (e.g., name ; location). Does anyone have a code which can actually define how many of these pairs will appear on the form when loaded? For example if there are 5 points to be defined the form should have 5 pairs of these text boxes. The maximum number of these pairs is around 10 and I could make a form with a 'standard' number of 'pairs' but that's not really it. I've tried to attach the form but it did not work
View 9 Replies
View Related
Mar 31, 2008
I have a sub that I need to call multiple times. I'm trying to figure out if there is a better way to do this rather than write out the call each time:
AYForm = "UserForm2.txtAYMonths" & i
AYPForm = "UserForm2.txtAYPercent" & i
SummerForm = "UserForm2.txtSummerMonths" & i
SummerPForm = "UserForm2.txtSummerPercent" & i
JobForm = "UserForm2.cboJobClass" & i
NameForm = "UserForm2.txtName" & i
For i = 1 To 15
Call FormatAY((AYForm), (AYPForm), (SummerForm), (SummerPForm), (JobForm), (NameForm))
' Call FormatAY(UserForm2.txtAYMonths & i, UserForm2.txtAYPercent & i, UserForm2.txtSummerMonths & i, _
' UserForm2.txtSummerPercent & i, UserForm2.cboJobClass & i, UserForm2.txtName & i)
Next i
the sub FormatAY has six arguments; 5 textboxes and 1 combobox. Since the names of these items are the same except for a number (example UserForm2.cboJobClass1, UserForm2.cboJobClass2, UserForm2.cboJobClass3 etc...), I wanted to write a for loop and use a variable to represent the last number. Is this even possible? The couple of ways that I have tried this, I get either a member not found, or ByRef error.
View 6 Replies
View Related
Apr 9, 2009
I have a range of cells (A1:D5) identified within a worksheet (worksheetA) as part of a user input form.
When a certain condition exists, a routine is executed that copies a range of cells (C1:F5) from another worksheet (worksheetB) to this defined range. The copied area consists of two command buttons. They get copied and pasted no problem.
When I want to reset (blank it out of its contents) my range in worksheetA from I copy another range over of just a range of blank shaded cells. However, doing this does not eliminate the command buttons left in the range on previous pasting.
How do I delete these command buttons left from previous pasting activities when I wish to reset my range?
View 9 Replies
View Related
Jan 17, 2014
I have been having an internal debate of how my worksheets shoud be configured.
My workbook could potentially contain 300+ worksheets, 10 control buttons and around 20 macros.
Each worksheet contains data in a range ~ A1:AC40 with many blank cells.
Do you think a workbook with this many worksheets would be unmanageable given the Comp Spec of the user PC below?
Most of the cells are simply short text data.
System info
Processor: 2.3Ghz Dual Processor
RAM: 2GB
32bit
View 7 Replies
View Related
Feb 3, 2009
i try run macro with commandbutton, but it doesnt work. I insert button from "control toolbox" then i create a macro. Then i wrote this macro to "Private Sub" but it still doesnt work.
When i click on button, it only open editing macro, or select the button.
View 6 Replies
View Related
Aug 28, 2009
I made a userform to calculate the net weight of a package by taking the gross weight and deducting the amount of boxes included in the package. The most boxes possible is 25, so I used 25 command buttons numbered 1 to 25, so users only have to press the desired amount of boxes for the calculation to begin.
But that also means I've got 25 subs containing the same formula, and that's a bit much. I'm pretty sure this could be done more efficiently,
View 3 Replies
View Related
Sep 9, 2009
I have a multiple of command buttons that I want to make visible. I have the following code which I am trying to get to work. Maybe I am barking up the wrong tree.
View 2 Replies
View Related
Jan 4, 2008
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.....................
View 9 Replies
View Related
Sep 10, 2008
I have a workbook with some command buttons that I created a while back. The buttons used to work fine. But after installing a new computer, the spreadsheet no longer works. I can step through the code fine, but when I try to click on the button, nothing happens.
View 9 Replies
View Related
