I have used the Forms toolbar to create a command button on a chart.
However, the text for the command button is hidden. If I do an "Edit Text" on the button, it shows up, but otherwise the button appears blank, even when selected.
it's possible to change the caption (title) of a user form to a variable or cell value? Or does it always have to be hard coded?
View 2 Replies View RelatedIs there a way to program a VBA button so that its cell location is known. For ex., I have a button that says "Fail". I'd like to program it in such a way that when the user clicks on it, the text "Fail" is inserted into the cell next to it. How do I avoid hard-coding the cell reference?
View 5 Replies View RelatedI need a macro for button1. The name of button one is "on" when clicked I want it to put an "X" in A1 and then change the button name to "off" When clicked again I want it to delete the "X" in A1 and change the name back to "on". When clicked again it repeats the same process.
View 7 Replies View RelatedHow do you references the properties of a shape? I am having trouble with 2 different types. Firstly, I have a worksheet with a group of commandbuttons (all added using the Control toolbox) and I want to loop through them changing, for example, the enabled property of each to TRUE.
Secondly, I have some commandbuttons on a worksheet (added using the Forms toolbox). How would I loop through changing, say, the text on the buttons. I used the macro recorder to try to get an idea and got the following:
Sheet1.Shapes("button 15").Select
Selection.Characters.Text = "New Text"
Although this works, it is not very elegant, how can it be achieved without selecting the button each time, but rather by directly accessing the property.
I have a worksheet with 2 buttons labelled "Hide" and "Show". As the names imply, they allow the user to hide or show parts of the worksheet. I would like to combine them into one button and have the button label and the associated macro change with each press of the button. Here is what I have so far;
Sub SHOW_LEADS()
Rows("10:15").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Shapes("Button 20").Select
Selection.Characters.Text = "HIDE LEADS"
Range("A1").Select 'is there a better way to remove the focus from the button than selecting a cell off the button?
End Sub
Sub HIDE_LEADS()
Rows("10:14").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Shapes("Button 20").Select
Selection.Characters.Text = "SHOW LEADS"
Range("A1").Select
End Sub
These macros change the label fine after hiding or unhding the rows but I can't find the proper terms to use to change the macro associated with the button (if there is one?)
On creating a button to perform a macro, we are hiding columns.
Can the macro pick up that
If the columns E:J are hidden, then the text on the button to read 'Unhide'
If the columns E:J and unhidden, then text on the button to read 'Hide'
I am aware I can change what is written on the button via a macro, but can it see that the columns are hidden?
I have a problem with the changing the caption property of the option buttons to bold, italic and regular.
Is it some vb code you need to put in for the option buttons?
I'm a complete Excel and VBA newbie and would like to know if it's possible to change the output of OptionButtons from 'TRUE' or 'FALSE' to "1"/"2"/"3"/etc..
The name of each button is:
Rating 1
Rating 2
Rating 3
Rating 4
and so forth..
The idea is for each rating to output its assigned number such that if the user were to click "Rating 1", the result would be "1", "Rating 2" = 2 and so on. I'm trying to design a survey and these results will then be used to calculate an average rating. If this is possible, how would I need to write the code for it?
There is a 'Home' page with a button for each employee - clicking the button takes the user to the appropriate employee's timesheet
There is also an 'Add New Employee' button on the home page which brings up a form and allows a new employee to be added.
Each employee has only 1 worksheet (Which is created based on a blank template page)
When the 'Add New Employee' button is clicked a form pops up which ask for name, D.O.B etc. The user fills out the form and clicks ok. The template sheet is then copied, renamed to the employees name, and all relevent fields are added to the template sheet (based on the users input). A button is also generated on the home page with the employees name as a caption and when it is clicked it goes to employees timesheet.
I have almost got this working.
The only problem is this only works once, So For Example say i Add 'John' then i add 'Mary' on the home page i now have 2 buttons with 'John' and 'Mary' on them. The problem is as soon as a second employee is added (Mary), the first employees button(John) is linked to the second employee's Sheet rather than its own(Mary). and after the spreadsheet is closed the buttons dont work at all.
The link below has the spreadsheet i have made so far, you can test it out if my description doesnt make sense or you can see the code behind the 'add new employee' form:
############################################################
#### 'Add Employee' Button on Add New Employee Form ####
############################################################
Private Sub cmdadd_Click()
'check for Name
If Trim(Me.EmpName.Value) = "" Then ..............................
I have created a cover sheet with various command buttons linking into the available worksheets. I was wondering if it is possible to set the title of the command buttons by linking them to the text in a particular cell in each work sheet??
View 3 Replies View RelatedI currently upgrading some add-in to Excel 2007. This one concern a toolbar or Ribbon as they call it now. I am creating a toggle button using XML
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="rxTrading" label=" Trading" >
<group id="rxDownloadStockData" label="Download Stock Data" >
<button id="rxDownloadData" label="Download" onAction="rxDataStock_Download" />
<toggleButton id="rxEnableDisableDowwnload" label="Enable Download" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
How can I change the name of toggle button to "Disbale Download" after clicking on the toggle button? I am trying to find the answer on WROX Excel 200 VBA but I am lost.
After scouring the entire OzGrid, I have found the following two macros that partly satisfy what I want to achieve. BTW, following two macros are executed on Sheet2 and I want to achieve the following:
On Worksheet_Activate, show columns that match the criteria value in Sheet1.D4 (Sheet1.D4 contains a dropdown list with about 6 text values). In addition to that, if Sheet1.D4 value is blank then show all columns. Right now, it successfully shows columns that match the D4 value but does not work if that D4 value is left blank, instead it hides all columns in range (C:CV). So in short, to the first macro, I need to add the criteria if D4 is blank, then keep the sheet intact and don’t hide anything, just show all columns.Once on that sheet after the Worksheet_Activate event has occurred, with the second macro “ToggleColumnsVisibility” following things need to happen. BTW, currently this macro is assigned to a button (from Forms Toolbar).If cell D4 has a value, then the button’s caption should read “Show All” and when that button is pressed, it should show all columns in the range (C:CV), and the button's caption needs to change to “Show Selected”. In “Show Selected” mode, when that button is pressed, it needs to show all the columns that match the D4 value.If cell D4 is left blank, then the button's caption should read "No Action" and when the button is pressed, it should not do anything but just show all columns.
As you will notice in the header, my experience with Excel is very limited, so detailed instructions will be greatly appreciated. I am also hoping that proposed solution uses some of the efficient/effective ways of using loops such as described below (example taken from this website from this link http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm)
Sub TrueOrFalseFaster()
Dim bYesNo As Boolean
Dim i As Integer
bYesNo = (i = 5)
MsgBox bYesNo
End Sub
Sub ToggleTrueOrFalseFaster()
Dim bYesNo As Boolean
bYesNo = Not bYesNo
MsgBox bYesNo
End Sub .........
I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros.
I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.
I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
The Module to open the first is this:
I have a form that is built based on the number a person enters. For Example - if the person enters the number 2 then 2 ComboBoxes are entered on the form.
The problem that I am having is that the list is not showing when the form is loaded. In the Project Screen all is OK until the final step when the form is Shown.
I have created a form that converts latitude / longitude from Decimal Degrees to Degrees Minutes Seconds decimal seconds. I want my coworkers to be able to use the form, but I don't want them to see the excel window/worksheet when they open it up.... I thought I had received an email once that appeared to be an excel file but when you opened it, it was just a form. I have tried all I can think of to no avail.
View 3 Replies View RelatedThis 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
MyUserForm.Show
The above code works in a worksheet code section but not in a custom Module? Why? How can I make this call outside of the Worksheet section.
I recorded a macro to open the Form on the Data Menu. Sample Worksheet Attached. After starting the macro recorder I selected the Food table cell (located to the right on the worksheet). Then went to the Menu bar.... Data Forms... and Clicked new.... followed by close and then stopped the macro Recorder. It produced the following code.... which reflects a code error when run.
Sub McrEnterFood()
'
' McrEnterFood Macro
' Macro recorded 20/01/2010
Range("P1:U16").Select
ActiveSheet.ShowDataForm
End Sub
I have the following code that displays a form at a user defined time and if the user does not press "Stop" then the workbook saves and closes. The user can press stop then the workbook remains open.
Here is what I have where:
Admin_Auto_Shutdown = Yes or No
Admin_Auto_Shutdown_Time = 3:34pm or user defined time (This doesn't seem to work??)
'Auto Shutdown CloseandSave
If UCase(wb.Worksheets("Admin"). Range("Admin_Auto_Shutdown").Value) = "YES" Then
Application .OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
End If
Sub AutoShutdown()
Application.OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
Auto_Shutdown_Form.Show
End Sub
Now, my question is about a timer that I can show on a form. When the form is displayed I would like to give the user 30 seconds to press stop (and keep the workbook open) or to press proceed and save and close or to not do anything and the workbook would close and save when the timer reaches zero.
Code for user form which is missing most everything...
Private Sub Halt_Click()
'If user whats to continue without closing
Auto_Shutdown_Form.Hide
End Sub
Private Sub Proceed_Click()
'If user whats to save and close
Auto_Shutdown_Form.Hide
How do I add a timer to this code where it will run this at the end of the timer?
Auto_Shutdown_Form.Hide
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.
I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.
I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).
Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.
If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".
Is there a way to create a shortcut/button by hiding or showing a column? Example: I want to hide or show the whole column E by pressing a assign button or shortcut.
View 4 Replies View RelatedI'm using 2010. When I highlight rows or columns and select Group, the show (+) / hide (-) buttons would appear in the next to or above the header rows/columns.
The file I am working on is not showing these buttons. If I go to the Data tab & click "hide detail" or "show detail", it will hide my grouping if I'm in one of the cells of the grouping.
I can easily hide/unhide, but would rather group & click rather than highlight rows/columns.
how can I change the button/shape name of a Form Button in Excel 2003? I assigned a name using the NameBox, however if I try to change the name via the NameBox, it does and work and the old name remains.
View 4 Replies View RelatedI have a userform that adds and deletes employees. The add button works but im not sure how to get the delete button to work. I have a defined name of "Employee" that is row "A". What I would like to happen is that when a employee is selected from a userform list and the delete button is pressed the employee is deleted and the form is updated.
View 14 Replies View RelatedIs there a vba code to determine if a form button exists on a certain worksheet?
View 9 Replies View RelatedI've built a form in VBA which is working great. I now want to call it by creating a button in a worksheet and then assigning the Form macro to it so the user can just click a button. I've tried looking for it in the Assign Macro dialog and it isn't there (even though I've declared it as a public sub) and I can't call it from a normal Module as it says it's an undeclared variable.
View 3 Replies View RelatedCan I change font size & color for button form, normal practice for font is disabled when I select button
View 9 Replies View RelatedI have a macro associated to clicking a form button.
If possible, if a certian cell value is equal to 0, I want to diable this button. I do not wish for theuser to be able to run the macro associated with that button click.
If found something similar, but in my case, I cannot find the equivalent to CommandButton1, if that's even what I'm using.
If Worksheets("SHEET").Range("A1").Value = "" Then
CommandButton1.Enabled = False
