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've been trying to research how to do this for a while now and still can't figure it out. My impression is that this is fairly straightforward in Excel 2010 since the menus and ribbons are easily customizable but not so in Excel 2007.
So far I can get macro shown as a button in the Quick Access Toolbar. But the issue is that I can't organize them or customize the icons.
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?
Is there a way to change the text on a toggle button in the code below? Basically I would like for the toggle button to begin with the word "START" on it and then when clicked changed to the word "STOP". Of course then when the user clicks on "STOP" it changes back to "START".
I would like to set the value of a toggle button when the userform is opened so that it coincides with the what columns are hidden on screen. Below is simple example which I have not tested or debugged. It was just what i was thinking.
Sub Macro2() 'if col A is hidden If Columns("A").EntireColumn.Hidden = True Then 'make hidecalcs toggle button true (sunken) Userform1.HideCalcs.Value = True
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 read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown .State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
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?
Is it true that I have to enable macro and choose to trust access to the VBA project object model in order to create and run macros? It seems there is some security risk associated with making the workbook as macro enabled. What makes the commands run from the ribbon security free? Is it true that we can work around the macro security concern by creating a command button to the ribbon and run the macro from the command button? If yes, how come I could not find any discussion of creating the command buttons? I am using Excel 2007.
I have a pretty developed add-in I've created for use at work. It has 3 full custom ribbon tabs worth of buttons and boxes and other features that run all of the macros in the project. Everyone loves it. The only problem is, its only used when we work with customer statement excel files. When using Excel for other tasks it is annoying to have 3 extra, essentially useless tabs taking up space on the ribbon.
Is it possible to create a button and stick it on, say the Data tab, that will "Start" the add-in and make the 3 custom tabs visible? Ideally there would also be a button to "Close" the add-in or hide the extra tabs until they're needed as well, but how to customize for my specific needs.
I have a workbook that has a userform with some simple code to translate formulas from .FormulaLocal to .Formula, and back.
I use that workbook a lot to translate formulas I find on here to something my German XL2007 will understand. I thought it would be neat, as well as giving me the opportunity to learn something new, to have that functionality as an add-in, rather than having the workbook open all the time. It is easy enough to save the wb as an add-in, and I can see it in my add-in list, install or un-install it. What i can't figure out is how to put a button on the xl07 ribbon when the add in is installed so i can access it.
In a program that I'm intimately familiar with (Excel) I seem to find it annoying. ...or at least in my limited usage thus far it's been that way. Takes up a lot of screen real estate and seems to be a bit of a cluttered jumbled mess of text and graphics.
For programs I use less frequently (Publisher) it seems to be helpful. I can sorta right-click my way through lots of things and get stuff done. That's kinda nice.
For a program I use moderately (Word) it's sort of a toss-up. I can't find stuff easily, but once I figure it out, it seems like it's a more efficient design.
I imported an Excel spreadsheet from a computer running an older version of Excel to my computer with Excel 2007. I opened up the specially designed add-ins tab and started a new worksheet which I then saved. When the programme was closed and re-opened, the add-ins tab was missing so I could not continue working on my new file.
Is this a compatibility issue between the versions of Excel (yet everything worked fine when freshly opened)? Following the help instructions I could not recover the bespoke add-in tab.
how I get this back each time I open the file? It is important I get the bespoke add-ins as they are designed to input runners taking part in a race and working out the results afterwards in various categories.
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?)
I'm making a Userform for commands I use a lot, like concatenation and highlighting cells. I placed a command button on the sheet that pops up whenever I open Excel. It's a bit awkward to have it on the sheet. Is there a way to place the button on the ribbon to get it out of the way? (I'm on an intermediary level with VBA
Another alternative would be to make a hide button on the userform, I guess. Once it disappears though I'd probably need another button to call it up again.
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 ..............................
Is there a way to get a sound toggle button on excel?
What I need is a button to turn the sound alerts on and off on my spreadsheet. Ideally I'd like a button that either changed an image to the speaker image when on and the speaker image with line through it when off. Is that possible?
If not, is it possible to have a button that just says: "Sound:ON" or "Sound:OFF"?
I guess it could be done with tick boxes, or drop down options.
I have the code below for creating a stopwatch in excel. I was trying to see if there is a way to change this code to assign it to a toggle button so if you hit one part the stopwatch would start and then the other it would stop.