How 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'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?
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 .........
Is 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?
I 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.
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.
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?)
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??
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 have just noticed that there is no defult event for the running code after updating a label caption. I would like to have like a textbox does (Change,AfterUpdate). to run code on the label caption change. could it be done with activex?? can it be done at all.??
Background: I currently have an some VBA code that will kick off a series of calculations on a remote server (7 calcs). I am showing a modeless form, to allow the user to continue to work in Excel while the external calculation is taking place. The code checks every few seconds (using .OnTime) to check the status of the calcs, and as one finishes, it starts the next calc, and updates the form (ie - "Running Calc #3 of 7", etc).
Problem: when I change the label on the form, it gives the form focus. So, if the user is in the middle of doing something, they are suddenly stopped, and have to click back on the spreadsheet to continue.
Questions: Is there a way to update content (label) on a form, without that form getting focus? If not, is there another way to provide updated feedback without disrupting what the user is doing? My fallback is to simply not update the form with progress reports, but since the calcs can take 5min or so, it would be good if they knew things were progressing.
I present a form (a calendar) twice in my routine to the user but I want to change the Caption of the Label on each pass?
e.g.
The first time it's displayed: 'Please select a start date' (calendar closes & goes off and does stuff) ... and on the 2nd display: 'Please select an end date'.
I am trying to edit a textbox caption in an active chart with a macro and cannot figure out the correct way to reference the text property. I have been successful in editing other items within this chart via the complete code below (i currently have textbox edit commented out)
[Code].....
I know i can set it up with a helper cell to have the textbox reference a range (that updates with the text i want) but i'd to try and edit the text without the use of helper cells to try and make this a little more break resistant.
I needed to put a Checkbox on a Worksheet. The standard Checkbox options in Excel FormControl and ActiveX Control were too small. I found a work-around using the following macro attached to an ActiveX Label that was formatted as Wingdings font. The macro basically changes the character from a empty box Wingdings Chr(168) to a checked box Wingdings Chr(254) when the user clicks on the label.
Private Sub Label1_Click() If Label1.Caption = Chr(254) Then Label1.Caption = Chr(168)
[Code]....
But I get this error on an ErrorHandler that I have included in the macro. "An error has occurred. Error number =438. Error Description=Object doesn't support this property or method."
I am trying to change checkboxes caption name using a loop so that I don't have to change them individually. Here is a simplified example of what want to do:
1) I save the caption names in a array 2) I run through a loop to rename each checkboxes using the names saved in the array
Sub change_Checkbox_Caption Dim MyArray(1to3) as string Dim IndexMyArray as integer MyArray(1) = "YES" MyArray(2) = "No" MyArray(3) = "Maybe"
For a form that dynamically creates a series of text boxes and labels, does anyone know how to change the caption of a dynamically created label to display the value of a cell in a worksheet? The cell in the worksheet changes according to input on the text boxes. In the form's code, I have:
Dim newLblFreq As MSForms.Label Set newLblFreq = Me.Controls.Add("Forms.Label.1", "lblfreq" & i, True) newLblFreq.Caption = Range("V" & i).Text & " Hz"
But once the label is created, the caption won't change, even if Range("V" & i) changes...............
I'm amtepting to populate some labels from some predefined strings based on the scrollbar value. I have these codes:
Option Explicit Public Meddelande1 As String, Meddelande2 As String, Meddelande3 As String, Meddelande4 As String, Meddelande5 As String, Meddelande6 As String, Meddelande7 As String, Meddelande8 As String, Meddelande9 As String
Private Sub UserForm_Initialize() On Error Resume Next Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False Application. ScreenUpdating = False Workbooks.Open "V:allaBeredningKontrollsystemetKontrollsystemet.xls", ReadOnly:=True Sheets("Meddelanden").Activate Meddelande1 = Range("B2").Text Meddelande2 = Range("B3").Text....................
As an aid, I have a label at the bottom of the form and when a person tabs onto a textbox that labels then states what you are supposed to input into that textbox along with an example. When you tab onto another textbox the caption on the label changes to give another explanation and example.
I attempted to insert an if statement into the userform which states that if a textbox is enabled then the caption of the label = "Example"
If textbox1.enabled = True Then label1.caption = "Explanation 1, Example 1" If textbox2.enabled = True Then label1.caption = "Explanation 2, Example 2" End If End If
I have a button (group containing and add and delete button).
I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row).
Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row.
This will allow me to add/insert rows by the button located at that row
The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp)
I also note that when a group is copied, it has the same shape name as that copied. I want to keep the add and delete shape within the group (BtnGrp). I do not want to select a cell or row or enter a row number to delete etc.
refer to sample workbook attached. Currently only has one record row.
I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.
I have searched and read about this issue on several forums, and they all indicate that i need to use something like
application.caller.topleftcell.row
to get the row number and
application.caller.topleftcell.column
to get the column number.
However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.
i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.
Private Sub Workbook_Open() Dim name Dim time As Integer time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")
I have a bit of code that I saved as an add-in. The code does a couple things: (1) Creates a right-click button that on action (2) draws a rectangular auto-shape that is the size of any selected range.
The add-in seems to loaded correctly.
However, when I launch a new excel sheet, my intent was to have the code (the right click option) be available whenever I opened Excel.
