I want to set up a cell so that when the cursor is on the cell a drop down option appears allowing the selection of a yes or no to be shown in the cell. What's this called and how do I set it up?
View 3 RepliesI 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.
RateCalcApp.xlsm
I have a workbook with 2 sheets. Sheet1 has a textbox and 2 option buttons named "north"and "south", and 2 command buttons named "send"and "retrieve".
Also a lot of other boxes, but they're not relevant.
I enter a unique number in the textbox, press one of the option buttons and press "send".
All data is now written in sheet2 in one row.
this all works ok.
Sometimes I need to change some data, so I enter the number in the textbox and press "retrieve". I get all the data back on sheet1, except the option buttons don't change correct. If on sheet2 the value = "north" I would like optionbutton1 to have the black dot and if the value="south" optionbutton2 should have the dot.
Now it's like they are on strike. they just don't react folowing the value of sheet2,collumn G, row "x".
I have done much searching, found some relevant, similar postings, but I have not been able to make it work for my code. I want to have multiple option buttons and have a macro determine which one is selected. One similar posting had macros run from the clicking on the option button, but I want the macro to determine which is clicked and then return some functions. From my research...this is my code.
Sub temp()
If OptionButton1.Value = True Then
Range("C1").Select
Selection = "OB1"
Else 'assuming two buttons and first is false
Range("C1").Select
Selection = "OB2"
End If
End Sub
This gives me an "Object Required" error on the first line of the code.
I have the following formula =IF((F3=1)*(H3=1)*AND(J3=1),1,0) and I do need to add the option C1 besides 1 so it would be something like this...
=IF((F3=1orC1)*(H3=1orC1)*AND(J3=1orC1),1,0)
I have a userform that has 3 option buttons (1, 2 & 3) each of these option buttons have a Label next to them, Label (1, 2,& 3) next to that I have 3 textboxes that are formatted for currency. Each label represents a dollar value $10.00, $20.00 & $30.00
OptionButtom Label TextBox
1 Month Member x $10.00 $.00
2 Month Member x $20.00 $.00
3 Month Member x$30.00 $.00
When one of the OptionButtons is selected I want the corresponding label dollar value to be multiplied by the OptionButton and that value placed in the TextBox
i.e. if OptionButton 1 is select the TextBox would have a value of $10.00
if OptionButton 2 is selected then TextBox would have a value of $40.00
if OptionButton 3 is selected then TextBox would have a value of $90.00
Lastly, if 1 Option Button is selected and then changed to another of the 2 buttons, the TextBox value would reset to $.00
I have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.
View 3 Replies View Relatedsee the attached below.
A few things to sort if I may
How do I get the listboxes to pre-populate based on the Userid and Option Button selection? Furthermore, write back to the correct cell once the selection has been made?
How can I get the combobox to remove blanks in the Userid? (if each cell in row2 has to be populated then so be it)
ToolMatrix3.xlsm
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
I have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
View 14 Replies View RelatedThe below code works well. However I would like to give the users an option to opt out of running the code when the message box appears.
Code:
Sub delete_sheets()
Beep
MsgBox "This will delete All Sheets but the active sheet"
*****this is where I would like to use an additional message box or include it in the above message box an option for the user to cancel this code****
Range("XA1") = ActiveSheet.Name
ActiveSheet.Name = ("Delete_Sheets")
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If Not ws.Name = "Delete_Sheets" Then ws.Delete
Next ws
[code]....
I have a userform with 5 listboxes on the first page, each with multiple options. They are all set up so any number of options can be chosen.
2 are populated by the 'add item' method, and the other 3 pull data from 3 columns of the database when it's initialised to ensure all possible options are included.
My question is this - as there may be many options in each listbox to scroll through, if someone wanted to search on all the options in the listbox, it would be easier to have a 'Select All' option at the top, but I have no idea if this is possible, let alone how to do this -
I am trying to white a macro that takes a file path from a cell value and opens that file. When there is no file path in the cell, it prompts a dialog box. This part is pretty much working. However, I would like the file path selected from the dialog box to be added to the cell in the original workbook.
Sub Reports()
Dim path1 As String
path1 = Range("B20").Value
On Error Resume Next
'checks if there is already a file path'
Workbooks.Open filename:= _
path1, Updatelinks:=1
'if no file path then opens dialog box'
If Error.Number <> 0 Then
Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
End If
End Sub
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
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).
Excel 2013
I am using the data validation option on a range of cells.
On the 'Error Alert' tab I am checking the box for 'Show error alert after invalid data is entered', selecting the 'Stop' style of warning (which should completely prevent entry of invalid data), but the cells with the data validation still allow entry of invalid data. No warning is given either that the data is invalid.
Public Sub OptionButton1_click()
Sheet1.Select
ActiveSheet.OLEObjects("OptionButton1").Interior.Font.Bold = True
End Sub
The problem i have is when i click on one of the option buttons it should change the cells ive highlighted to bold.
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.
I'm not sure of the correct mathematical term for the addition I have to do but here is my example:
in this row are the values
1 2 3 4 5
In the row below I want to add the numbers so the result is
1 3 6 10 15
basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?
i have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
I'm trying to highlight a bunch of cells in clusters not close together, and then invert the selection so that all cells BUT these are selected (therefore capturing all possible cells that someone could scroll to). In XL03 you could go to File > Invert Selection and it would work fine. But in XL07 which I have now, there doesn't seem to be a way to do it. Also, when using ctrl^a to highlight everything, and while still holding control in XL03 you could de-select specific cells, not so much in 07. I need to know if there's a way to do it with no macros involved. If not, I need a macro to do such a function on any given worksheet.
View 6 Replies View RelatedI have selected a range in a worksheet using VBA. How can I now code so that if any cell in the selection has a value greater than 0 , then
Put up a Message. I do not know visually what has been selected, and it will change from sheet to sheet so I cant hard code the range.
If all cells have a value of nothing or zero, I will delete the entire selection.
If any row has cells with a value > 0, I will keep it and move to the next row
Then, delete all rows where all cells have a value of nothing or 0
I am assigned to a very tedious task which requires me to lock selectively, cells from Jan through to Dec. Instead clicking the cells and selecting the various options from the menu bar, I would like to do it the other way.
If I want to go locking a cell upon selecting it, what are the VB codings?
How can I sort a 7 column selection and then paste columns 1-4, and 7 to the new location? Columns 5 and 6 should be ignored for the most part. Also, how can I obtain the first values of the column 5 and 6 selection to? I want to use them to name a file. This is how I am selecting the range.
Dim rng As Range
'Copy Selection
Set rng = Selection
rng.Copy Destination:=Range("A8")
This is how I name my files. I am getting the values from the form.
Dim strFileName As String
strFileName = .DateBox.Value & " " & .FromStoreBox.Value & " " & _
.ToStoreBox.Value & " " & .RequestedByBox.Value & " " & ".xls"
I would like to create a spreadsheet that allows me to have a drop down list (in this case I diagnosis - from a list of shoulder, knee and ankle). I have used data validation to do this part. I would like to then have the next column automatically select the appropriate outcome measure (shoulder = DASH, Knee = Berg balance, Ankle =BPI) and display this next to the diagnosis and in the final outcome measure box. I would then like this to limit the possible entry details for the score boxes both at initial assessment and final (see D14, D15, D16 respectively).
I have attached a work sheet too - OP outcome measures.xlsx
when I select a value from my drop down list how can I fill in say.. cell A50 with a number automatically?
Pretty much on a drop down selection I want to fill in another cell.
If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.
View 4 Replies View RelatedI need a macro that can cut and paste a selection of 3 cells to a location one row above and 3 cells to the left. I want to run it each time manually from the right cell among the 3. the macro should therefore (when standing on the right cell of the 3):
1. select 2 more cells to the left
2. cut the selection
3. move 3 cells to the left and one cell up
4. paste
5. move the cursor back to the starting location
see attached pic for example.macro needed.JPG
I'm trying to select an x number of random selections within the selection.
Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them
This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?