Selecting Optionbutton Using Vba |
Selecting Optionbutton Using Vba - Excel |
|
I have two option buttons on each of five spreadsheets in my Excel workbook.
Selecting the first option button enables an x to be written to a cell when that cell is clicked with the mouse.
Selecting the second option button disables the writing of the x when the cell is clicked with the mouse.
I have these working quite well.
My problem is that I want the second option button selected (disable writing of x) when I enter the sheet. I'm pretty certain that I need the code in Sub Worksheet_Activate() but I haven't been able to find the VBA statement to set the second option button on.
Also, unlike having the option buttons on a form, when I select the option button on the spreadsheet in Design mode I can see no property sheet indicating the name, value, etc of the option button.
Selecting the first option button enables an x to be written to a cell when that cell is clicked with the mouse.
Selecting the second option button disables the writing of the x when the cell is clicked with the mouse.
I have these working quite well.
My problem is that I want the second option button selected (disable writing of x) when I enter the sheet. I'm pretty certain that I need the code in Sub Worksheet_Activate() but I haven't been able to find the VBA statement to set the second option button on.
Also, unlike having the option buttons on a form, when I select the option button on the spreadsheet in Design mode I can see no property sheet indicating the name, value, etc of the option button.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
Hi guys,
I was wondering if it is possible to change the color of a command button after it's been clicked?
This is to allow the user to identify which buttons have already been clicked.
Thanks!
I was wondering if it is possible to change the color of a command button after it's been clicked?
This is to allow the user to identify which buttons have already been clicked.
Thanks!
Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?
Thanks in advance.
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?
Thanks in advance.
Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.
I am running Excel 2011 for Mac.
I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.
Appreciate any help, let me know if you have any further questions.
Hunter
I am running Excel 2011 for Mac.
I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.
Appreciate any help, let me know if you have any further questions.
Hunter
Hi All,
I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :
Code:
Thanks a lot for your help in advance.
I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :
Code:
Private Sub CommandButton2_Click()
'REMOVE SELECTION
Dim I As Long
With ListBox1
For I = .ListCount - 1 To 0 Step -1
If .Selected(I) Then
.RemoveItem I
Sheets("URL List").Rows(I + 2).EntireRow.Delete
End If
Next I
End With
End Sub
Thanks a lot for your help in advance.
I have a merged cell which contains a text with multiple lines.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.
Hi all, the title pretty much says it all, I have a form where I am trying to create a button that will pop up a window where the user will browse to the desired folder and once that folder is selected, the path will be copied as a text string into the cell adjacent. My command Button cell is in B3 and the path should be shown in C3.
Can anyone help?
Thanks
Can anyone help?
Thanks
Hello -
I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.
In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.
I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.
Thanks for your suggestions.
I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.
In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.
I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.
Thanks for your suggestions.
I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.
I'm new to macros and would appreciate any help this board can offer. Thanks...
I'm new to macros and would appreciate any help this board can offer. Thanks...
hi,
I have a login form with an Exit button. I want my workbook to close as I click Exit button.
I have a login form with an Exit button. I want my workbook to close as I click Exit button.
I recently moved into a new office and I am using a brand new computer on our network. When trying to edit an existing file, I'm not able to select a single cell and type. After clicking on a cell, if I move the mouse at all (even without pressing the left button) it just continues highlighting cells no matter where I move the mouse. I am also not able to click on the tool bars at all. I have to actually Ctrl-Alt_del to get out of the program. I'm sure this is something simple, but I don't know that much about this program. Any help would be greatly appreciated.
Hello,
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
I'm pretty new at VBA and was wondering if you could help me out on this:
I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.
thank you so much and would really appreciate to hear from anyone soon.
Hello everyone!!!!
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!
Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
Hi,
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
after sorting data in ascending order, how do i unsort it to get back the original format? i can't find the option anywhere on the excel ribbon for this
I have a excel spreedsheet that contain external data. I would like to put a
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
Hello,
I want to protect a cell that I have a drop down list in, but I still want the drop down list to work.
When I try to protect it, the user is not able to use the drop down list to select other items. I didn't see an option on the protect sheet area.
Thanks for any help.
Nick
I want to protect a cell that I have a drop down list in, but I still want the drop down list to work.
When I try to protect it, the user is not able to use the drop down list to select other items. I didn't see an option on the protect sheet area.
Thanks for any help.
Nick
I would like to know if it is possible to deselect 1 cell after selecting
many cells using the CTL-Click method. For example, you are indivdually
selecting cells in a worksheet and you accdidently select a cell you dont
need, how can you deselect that one cell without deselecting all of them and
starting over again?
many cells using the CTL-Click method. For example, you are indivdually
selecting cells in a worksheet and you accdidently select a cell you dont
need, how can you deselect that one cell without deselecting all of them and
starting over again?