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.
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
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 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?
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?
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
I created a frame in a spreadsheet I put in 2 options buttons that I called Left the first one and Right the second one. I got 2 cells lets say C10 and G10 in these 2 cells I write the cell number values. When I click on button one or Left, I need the cell C10 be set focussed and write a number e.j 5. However, when click on the second butt to write a number e.j 7, the number in the cell C10 still there, so, what I need is a cero or an empty cell C10, and viceversa. Could you help to write a code for this problem.
Attached I have kind of a score card, when you open the tab named TEAM1 a userform pops up and I pick the partners that played together, then I can pick an option button labeled 0,1,2 or 3, when it unloads, is it possible to make it put the corresponding number by their names in the empty row (W) of the week they are playing?, and not just the 1st open row available?
For example, if I pick the names Ryan and Stu the number will go under week 10 (X3) and not find the empty cell in (J3)?, I have no clue how to make this happen, but I made a userform to use, to try and make it work.
I use Excel2007 and saved this workbook as excel2003, the combo box doesn't dropdown anymore? I don't understand why....is it just settings? If you could have a look at the code I use for the option buttons...when I select the first optionbutton it calls the msgbox without selecting the offset cells. From optionbutton2 onwards it works fine.
I know that there is an ocean of information out there with regards to option buttons, but for the life of me I am unable to find what it is I am looking for. So I will try to explain what I am trying to do as best as possible. In my workbook I have 4 sheets. Sheet 1 contains all the data. The remaining sheets have taken all the data and broken it down by machine type. IE(carton errector, Carrier errector, etc) I have a series of user forms that you would navigate through to perform a search. For This example frmMain_menu is where the user would start out. I only have code added for the first button (Pearson).
This will bring up another form with more options for the user to select. Currently I only have code for the Carton Errector button. Once the user has selected that button frmCarton_Errector pops up and it has a series of 12 option buttons to choose from. What I am trying to accomplish is say the user selects cylinders, I would like once the search button is clicked for the program to is search sheet2 col f for the caption value of the option button selected. Any hits that it might find I would like to display certain datafrom the rows in a list box.(IE col A,B,G)
I have in a Useform 4 OptionButtons. This is working Ok to a point. My Question is, I have 12 images. I like to show a particular image depending on what option is choosen. As of right now if Optionbutton1 is selected. The right image is shown. But if 2 OptionButtons are selected the wrong image can be shown. So, it leads me to think I need and if or statment in the code? If so how can this be done in a example.
Private Sub OptionButton1_Click() ActiveSheet.Unprotect If OptionButton1.Value = True Then Sheets("HAAS INT. NPT THD. 1 PASS").Range("E10") = "M03" '= CW RH HAND THREAD
If OptionButton1.Value = True Then Me.Image2.Visible = True 'Conventional Int. Left Hand If OptionButton1.Value = True Then Me.Image3.Visible = False 'Climb Int. Left Hand If OptionButton1.Value = True Then Me.Image4.Visible = False 'Climb Int. Right Hand If OptionButton1.Value = True Then Me.Image5.Visible = False 'Conventional Int. Right Hand
ActiveSheet.Protect End Sub
Private Sub OptionButton2_Click() ActiveSheet.Unprotect.................
I have a spreadsheet to keep track of purchase orders, and I use groups of option buttons to run macros to filter the info by category using auto filters. Not sure if I'm explaining this well, but as an example, I have one set of option buttons for whether the items are late, and one for whether I have flagged the order for some reason, and a couple of others. It does everything I want, basically.
I have a button which resets all the autofilters in one go, but the problem is it doesn't put the option buttons back to the "Show all" option for their categories. I tried recording a macro and clicking in the "Show all" option so I could look in the code to see how it refers to it, but it just says to run the macro, nothing about selecting "Show all".
Also, the way I reset all the autofilters is to have a macro that goes to every autofilter in turn and selects "(All)". Is there a better way of doing this?
I have a spreadsheet with a set of 5 option buttons within a group box. What i want to be able to do is select an option and depending on the option an image above changes to reflect the option selected.
Is there a way to have mulitple groups of option buttons? I need to have different sections that can choose from a group of options buttons, right now when I add more buttons, only one button is selected. I'm not seeing where I can group them (the group option will not hightlight). I created the Options Buttons using the Form tool bar, if that makes a difference.
I noticed that it has a frame, and within the frame are three option buttons. I became quite curious as to the relationship between option buttons and the frame. With no coding on my part, the little buttons seem to mystically sense their presence inside of a frame, and dutifully acquiesce to whichever new option is clicked, so only one option at a time may be true. I decided to experiment. I created one lone option button outside of the sacred frame. I was able to click it on, and, lo and behold, I could not click it off! When I cut and pasted it inside the frame, with its brethern, it automatically became obedient. Well, I pasted it again outside the frame, and experimented with how I might turn it off. Now, my perverse nature began to sense that such behavior is the venue of a checkbox and not an optionbox. But, like Don Quixote, jousting at windmills, I was determined to have my way. The only methods I found to turn a lone ranger option box off, once checked, was to use the keydown option, and set it to false, which is obscure and tacky to say the least, or to place a label next to it, entitled, "turn the blasted thing off" and, in its click event, set the option button to false.
What I deduce from all of this is that the proper function of the option button lies within the boudaries of a frame, with one or more brethen option buttions. Am I correct in my surmize? And otherwise, one should be using a check box, which may be toggled on or off to the heart's content?
Hmmm. I have not thought to test the function of checkboxes residing within a frame (until just now!).
Experimentation reveals that if I add several check boxes to the frame containing the option buttons, there is no apparent affect. The option buttions still obey the frame rules for radio button logic, and the check boxes may be toggled on and off at will.
So, I ask myself the next obvious philisophical question, namely "what would be the use for a lone option button that, once turned on, could NEVER be turned off.
Well, in our society, I can think of two obvious applications.
One: "Have you ever been a member of the Communist Party"
Two: "Have you ever been attracted to the same sex."
Now, in our society, once there is the slightest hint of either option, it can NEVER be turned off. Even if you need better reading glasses, and you mistook "same sex" for "SOME sex", no matter, you checked the option and now you are branded.
And if you want to cancel my form, think again, because my VBA code updates with ever change event.
This option button philosophy suggests to me an interesting social commentary. I was once asked to write a literary analysis of the movie, "Brokeback Mountain" and the short storie by Annie Proulx. I asked various people, randomly, if they had seen the movie. I asked one African American male, and he recoiled from me and say "Ewww, you would watch that movie." In his mind, I had checked that option box, and there was no turning back. I gave him the following explanation. "Imagine an athlete who was notorious for seducing over 1000 women, but suddenly the tabloids revealed that as a teenager, he had one same-sex involvement. What would you say of him?" Of course the answer was that he was one of those "brokeback mountain" folks. So, then I asked him, "well, suppose someone was notorious for 1000 same sex involvements, but the tabloids revealed that, as a teenager, he had a relationship, with a girl?" Well, we know the reaction, same thing "a brokeback mountain fellow."
So, I progressed to a similar analogy. I said, "You see me as Caucasian, but, suppose you learned that my great great great great grandparent was African? Would you then see me as African-American? But, if your great great great great grandparent had been Caucasian, would you consider yourself Caucasian." He became quite flustered and said "that is different."
But, aha, is it really so different? Therefore, in our society, in our values, in our minds, in our stereotyping of others, we do require the data type of the lone optionbox, which can only be checked ON, and once on, can NEVER be checked off.
And, yes, this post is pertinent to excel vba forms, because the purpose of our forms and our datastructures is to accurately mirror our thinking, our values, and our society.
If I have two optionbuttons (controls) on a sheet, may I define the same linkedcell for these two optionbuttons, or only for one of them ? My question arise from the fact that I meet sometimes an error message ("The cell or chart you are trying to chage is protected...") although the linkedcell is unlocked on the protected sheet (which is not the sheet where the two optionbuttons are placed).
My problem is that no matter which option button is selected, all I get is the results for Case 2 and then the vlookup doesn't work. So if the user wants to cancel and reenter a new clock number they have to close the form and manually reopen it.
This was working until I decided I wanted a vbOKCancel instead of a VbOkOnly. However the uploader is not working for me.
The best I can do is a link for OneDrive [URL] ......
I have a spreadsheet with three clusters of option buttons, with 3 option buttons in each cluster, for a total of 9 buttons. All three clusters appear to be associated, as only one of the 9 buttons can be selected at one time. I need each cluster to be independent of the other, as each will control a different calculation.
I have a group of 3 Option Buttons that are grouped by a Group Box. I added another Option Button and placed it in the third position, moving the button that was originally third to the bottom. When I select the fourth button (that was originally the third of three) it still returns a 3 in the linked cell, and when the new button (placed in the third position) is selected, the linked cell returns a 4. How do I change the order that the linked cell sees? In the attached spreadsheet, the Group Box in question is the Pricing Method on the Tow worksheet.
I was re-reading Chnadoo's thread below on using a hyper link to drive a formula, and was wondering if there was a way to do the same thing with shapes. Interactive Dashboard in Excel using Hyperlinks | Chandoo.org - Learn Microsoft Excel Online I tried doing it where I used a hyper link on each shape to link to a cell containing a number, that in turn would be picked up by the cell that I would want to be incororated in to an INDEX function.
Let's say cells A2 to D2 contain the numbers 1 to 4. Cells B3 to D3 have an oval shape which are each linked to the cell above it by a hyperlink.
Upon selecition of any of the shapes I was trying to get the corresponding number to appear in cell P1 by way of very basic VBA. I was trying to get it to pick up the number by way of referring to the ActiveCell, but cell P1 wouldn't update automatically upon each selection of a new shape.
I know there are other ways to get what I want with scroll bars, spin buttons etc but I'm interested to know if it can be done with shapes.
I have been looking on here for a solution, but did not find the right answer, so I hope someone can help me with this. What I like to do is on a userform set up a "frameoptions" with two option buttons and depanding on what option I click either frame A shows or frame B shows.
When I click on "option" one it shows "frame 1" and if I click on "option two" it should show "frame two". both frames I would like to have on the exact same location of the userform. So the look of the userform stays the same. Is there a solution to this?