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.
I have 5 Optionbuttons (in userform) named: Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5.
When I open workbook Optionbutton1 (Sheet1) is activated, but what kind of loop(?) I need, if I want activate same named optionbutton than activate sheet. Example: I activate sheet3 then Optionbutton3 (named sheet3) have to activated also.
I tried simple codes, First worksheet: Private Sub Worksheet_Activate() OptionButton1.Value = True End Sub
Second worksheet: Private Sub Worksheet_Activate() OptionButton1.Value = False OptionButton2.Value = True End Sub
I have a userform on which there is a frame containing 8 option boxes. After a query, the results are displayed in the form. If I have a value of 1 in a cell, optionbox1 is checked; a value of 2 checks optionbox2, etc. It works great EXCEPT when first initialized. At that point, it checks the last optionbox, even if the number is 1. I have built a next and previous feature to scan the data, and when I return to the first entry, the correct box is checked. I tried coding blanks into the fields prior to populating them, but I still get the same results. Is there some explanation available so that I may remedy this? I'd really like the first piece of data to be correct.
I have a workbook containing several sheets, each sheet has a large number of ActiveX check box controls on it.
The controls are presented in groups of three to capture responses to a question (Y/N/NA). If one of the three check boxes is set to True, the other two associated check boxes must be set to False.
What I want to do is avoid having to have an On_Click event sub for every single check box.
I have written a function that will handle updating the related check boxes but I am unsure how to call this function, passing it the name of the clicked Check Box whenever any check box is clicked.
Here is my current code with an On_Click event being used to call the function:
Private Sub chk100_01Y_Click()
' Want to replace this with a dynamic sub that will be invoked ' when any Check Box is clicked and pass the name of that Check ' box to the function
I have the following code. I need the textbox values to pass to the cells when the option button (OB) is "moved away from"; that is, when I fill in the boxes while uner OB1, then select OB2, I want the values I put in to pass to the cells. Right now, the values passing to the cells listed under OB 1 are the values that are brought in under the enter event for OB2.
I have created a Group Box in Excel with 4 Option Buttons in the group. I have also created a Command Button which currently, when clicked, changes to a worksheet I specified. I am trying to have the Comand Button, when clicked, look at the selected Option Buttons in the Group Box and change to the worksheet specified by the selected Option Button. I have tried an IF..THEN statement with no success. code below that I have tried.
Private Sub CommandButton1_Click() If AM40 = 1 Then Sheets("Billboards(1)").Select 'ElseIf AM40 = 2 Then ' Sheets("Live Events Feedback(1)").Select End If End Sub
Ihave 2 functions which are called in my main program which should return a value due to what the user selects on a userform using option buttons and check boxes.
Function getnum() As Integer If userform1.OptionButton1.Value = True Then getnum = 1 End If If userform1.OptionButton1.Value = True Then getnum = 3 End If If userform1.OptionButton1.Value = True Then getnum = 5 End If End Function
Function getlevel() As Double If userform1.CheckBox1.Value = True Then getlevel = 1.2 End If.......................................
I have a user form with a group of several option buttons. When an optionbutton is selected, programatically, I want a worksheet's named column to be parsed through to find unique entries and then have those entries passed to the form's combobox. I've spent the last hour searching for an answer but to no success
Is there a simple solution, by changing something in the button properties? My current code below allows data from a userform to goto specific sheets and cells. Currently my option button named winbutton if true will display "true" in cell. i need it to be numerical value "1".
i did write code to search for the data..and if the data has more than one record then a button will be appear to find all data that related to entered data... and user will have option to press on the select button to select the wanted recored and delete it or update it....the problem is that the select button show the selected data in the form but its not selecting the right data in the sheet. So when i click on delete button it delete the first row which has the same data that entered by the user.
for example, user wants to see all data the belong to the user "Tim", the forms will show all data related to the use Tim... but i want to delete the selected row not the first row with the name of Tim!
i learnt the code from this link [url]
here is the codes for find. find all, and select
Private Sub cmbFind_Click()
Dim strFind, FirstAddress As String 'what to find Dim rSearch As Range 'range to search Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp)) strFind = Me.txt_num.Value 'what to look for Dim f As Integer With rSearch
I have a table of adressess and clients with contacts ie
a,b,c,d client,contact,address,select
in the list there are about 300 entries.
what i want is to put an S in the select column on those clients i am due to visit and then these records to be exported into say a word doc to print off so i have a list of visits i have to do with company contact and addresses
i dont use excel that often, and normally only for really stuff, so apologies, but i couldnt find a solution.
I have a spreadsheet of expenditure, with a column for a category of what was purchased, and then the amounts in the next column, arranged chronologically. eg
01/02/08 food 20.68 06/02/08 petrol 44.65 07/02/08 food 117.65
etc, with various oither columns for week nos (for averages), comments and the like.
Now I would like to do some analysis, for example the mean weekly food spend. I think this should involve checking the category column for "food", and if found, adding the adjacent cell (containing the amount spent) to the sum (and then obviously dividing by number of weeks) - but i cant work out how to do this!
I'm trying to create a formula that searches through a row and selects certain values. I have a list of individuals in rows followed by their 18 scores in columns. What I'm trying to do is only select the best 8 of those scores and add them together. I tried a few things but this is a little over my head.
I need to select the whole column ABOVE the active cell. Ctrl-Shift-UpArrow is no good because it stops at the first blank cell. And selecting the whole column is no good either because when I subsequently paste into the column, it pastes in all the empty cells of the column, meaning my worksheets expands from a few hundred rows to 1 million!
Shift-Home does what I want on rows. Is there an equivalent for columns?
So in my code I have two set ranges and want to select from one set range to another. The code I have now does not work because I cannot select Range("POBRF:PO"). This is the code.
[CODE] ARow = Range("A" & Rows.Count).End(xlUp).Row Set POBRF = Range("A1:A" & ARow) BRow = Range("I" & Rows.Count).End(xlUp).Row Set PO = Range("I1:I" & BRow) Range("POBRF:PO").Select [CODE/]
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192 Select Shift and R-Arrow so that C192 and D192 are selected Select Ctrl, Shift and Up Arrow so that C1:D192 are selected Select Shift and Down Arrow so that C2:D192 are selected
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
The main worksheet is for buttons to select emergency type and what group should be notified. Subsequent sheets will have different sets of contacts - Col A= Name, Col B = email address, Col C = Yes/No indicator.
The basic code works great as long as the names are on the primary worksheet. When I try to point the code to a different worksheet, the result displayed in the MSGBOX is null. I did this by putting "Worksheets(Sheet2)." in front of "Columns("B")." I double checked the sheet name.
If I select checkbox1 I want checkbox3 to be false and vice versa, thats simple enough. Everytime I click on one or the other checkboxes I have to reselect the one I want.
It takes two clicks to get a checkbox checked (True). My listbox will be populated by both lists in the "Checkboxes" code once I select a checkbox, then reselct to repopulate.
I have a table, that while writing a vba code I need to select the first column in the table (A) and the last column with data inj the table ( unknown which column it is). The last column might change (meaning that it sometimes column B, somestimes column Y etc...depends on the data). I need a way to select only the the first AND the last column in that table(actually it's the last column with data on that sheet). I'm sure there is must be a way I just don't know it!
I have 5 sheets in a workbook. After a code has run I want each sheet to be in A1 when they click on that tab (as there could be 50,000 rows on each), but when I try recording it it doesn't do it properly (Sometimes the A1 cell is active but the view is somewhere down the bottom of the sheet).
I have a macro that my coworker runs weekly to update info. The macro depends on data in a workbook that comes from another source who arbitrarily decides to rename things. I put into my code a 3 situation scenario.
1. If the sheet name = what it is supposed to then activate it 2. Else If the sheet name contains the word "Pivot" then activate it 3. Else load a userform that lists the worsheets in the workbook and allows the user to select the proper sheet.
The code I have is below. It works until I introduce the 3rd option. Is "Else" not the proper syntax here? Because when I run it, it jumps straight to the "Else" statement and loads the userform even if the sheet is named correctly or contains "Pivot" in its name.
Code: Sub FindPiv() Dim strWSName As String Dim s As Worksheet For Each s In ActiveWorkbook.Sheets
