Create Command Buttons Based On Worksheet Names
Jan 18, 2010
I am at it again and maybe I am asking to do something that is not possible. I am trying to build a program that will allow the user some flexibilty down the road if I am no longer here to support my code. I have a workbook that contains several sheets. The sheets are labled by equipment name. The Sheets themselves contain specific data with regards to the equipment. What I want to do is as follows. Populate a user form with command buttons based off of the worksheet names. The reason for this would be if 2 years from now we add a new piece of equipment all the user will have to do is add a sheet in the workbook and the user form would reflect the new sheet with a new button. Like wise if I delete a sheet in the workbook the user form would refelect that change as well.
View 14 Replies
ADVERTISEMENT
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
Apr 2, 2013
I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.
The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.
The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:
1. Enable all textboxes in the same row as the command button
2. Enable the other command button in the same row.
Here is the code I have so far that creates the textboxes and command buttons.
Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.
Code:
Private Sub CboTeamSelect_Change()
Application.ScreenUpdating = False
If CboGroupSelect.Value = "" Then Exit Sub
Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long
[code]....
View 2 Replies
View Related
Mar 25, 2012
I have some command buttons on a worksheet that I would like to disable, preferably make not visible, until a specific person logs in. I already have code to display worksheets, or hide them, depending on who is logged in. I would like to just add this code to the end of the login procedure, to make these buttons only visible when that person is logged in.
View 3 Replies
View Related
Jul 7, 2014
I'm trying to get a sheet filled in the order of the command buttons selected. It works like:
User clicks command button "A" in Sheet 1.
Macro runs that selects correct info in Sheet 2.
Info gets pasted in Sheet 3.
Repeat and Sheet 3 columns fill to the right with each command button clicked.
My issue is that I'm not sure what command to add in the macro to make it paste in the next available empty column. Right now all I can get it to do is paste over the info already there.
View 5 Replies
View Related
Jun 18, 2008
I'm trying to create a macro that creates new worksheets with buttons on them that have macros attached. I've been able to produce one worksheet with a button and macro, but when the function that copies the code is looped it crashes out of Excel.
I realise that the usual method of doing this would be by templates, but this code will be run on other people's computers so the template wouldn’t exist in the location that i had specified (I think with my limited experience with VBA - Please correct me if I'm wrong). I've also tried to copy the page with the button on, but this takes too long (even with screenupdating = false) as there are many sheets to be created.
This is a stripped down version of the code that I have created for the purpose of this forum. To recap, the function that creates a new page, with button, with macro attached works. It's when it's looped that it crashes out. I've stepped through the program, and it reaches the second message box, then it gives up on life ..........
View 9 Replies
View Related
Jun 16, 2008
Is it possible to use a macro to create a new worksheet with a button on it, that has a macro attached to it, that i can send out to people? I've looked at the Template example, but i'm not sure this will work when emailed as i'm unsure whether the template will be sent to them.
View 3 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 28, 2008
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
View 3 Replies
View Related
Aug 5, 2008
I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.
In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.
Sub Macro8()
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _
"=OFFSET(Sentiment!R3C1,0,0,COUNT(Sentiment!C1),1)"
End Sub
Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.
View 6 Replies
View Related
Jun 6, 2013
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
View 5 Replies
View Related
Aug 10, 2014
I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.
List of values:
Dairy NI
Dairy SI
Sheep and Beef
Equine
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture
Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!
View 1 Replies
View Related
Sep 2, 2007
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
View 9 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
Aug 17, 2006
Is it possible to create a number of command buttons, depending on the number of lines on a worksheet, and then have the code attached to the command buttons execute based on the position of the buttons on the sheet. For example, if I have a button positioned over, say A3, can the code execute based on the content of another cell in the same row. I imagine that the button would have to be linked to the cell, but I cannot see how that is achieved.
View 8 Replies
View Related
May 25, 2007
i am trying to access buttons like ' Format - Column - Hide'
View 5 Replies
View Related
Aug 23, 2007
I was creating command buttons and had trouble using the linked cell. I found an example on the forum here but the command buttons seem to have many different behaviors. On the attachment, "Button 1" and "Button 2" are the ones I created in Excel 2003. "Button A" and "Button B" are from an example I found on the forum.
1 and 2 can only be edited on design mode, and have the formula "=EMBED("Forms.OptionButton.1","")" by default (which yields an error when exiting the formula field). A and B can be edited at any time, and have an extra tab ("Control") on the Control Format dialog. This seems to allow the linked cell to be set and function pretty handily. Are A and B from a different version of Excel? Auto Merged Post;Here's the attachment:
View 3 Replies
View Related
Mar 6, 2014
I have a main worksheet that has two combo boxes. The first combo box has a list of machines. The second combo box brings up a list of models for the type of machine selected. I would like to insert a command button, that when pressed will open a different worksheet with the specs of the model selected in the second combo box. I am having trouble finding a starting point for the code to have the command button perform this.
View 6 Replies
View Related
Jan 13, 2013
This is what i have but its not working.
Code:
Private Sub UserForm_Initialize()
CommandButton4.Enabled = Not (Sheets("0").Range("J8") = "Off")
End Sub
View 3 Replies
View Related
Jul 8, 2013
I have 3 cpmmandbuttons in a spreadhseet named "Controls" . viz
Trim and Edit Base DataCreateReportArchive
They are always run in the same order.
My Aim is to disable and gray out the Archive button once its assigned macro is run(by clicking it) so that it activates only next time that the workbook is opened.
View 9 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
Sep 21, 2008
I need to get the code for a "copy" command button and "select all" command button. I need both for a text box.
Scenario: I click on the "select all" command button, it selects whatever is in the text box and then the "copy" command button copies all of that. So then I would be able to paste that in another program.
View 6 Replies
View Related
Feb 26, 2009
Does anyway to use a scroll bar to priortize a selected range?
I have included a simple example that I would like to rearrange with a scroll bar.
For example, I would want be able to select one row in the range and then place it either above the current position or any where in that range.
View 13 Replies
View Related
Jan 13, 2010
I have I command buttons on the sheet and when I delete a row, the postiion of the buttons is changing. Can I make it somehow so they never move when I add or delete rows?
View 2 Replies
View Related
