Can anyone help me in finding how to write the command in VBA to determine what Cell was selected in a worksheet? The objective is to know what Cell has been selected by double click event to pop up a userform with a calendar from which the user can select the date. I am looking at making it dynamic so I do not select a particular range.
Can this be achieved? I guess yes, but how?
I have a macro which formats charts. It takes a user selected chart and formats it.
I would like to allow the user to select many charts and process them all at once (instead of just one at a time).
The problem is that when I have X charts selected in Excel, the Selection object assumes the "ChartObjects" type which includes all charts in the worksheet and not only those which are selected!
If I do "Selection.Count" it will count all charts (selected or not) and if I do "Selection.Item(x).Name" I can access any chart on the worksheet and not only those which are selected.
Questions:
1. Is there anyway to determine which charts are selected by the user? Either using the selection object or any other method?
2. When I select multiple objects (chart and non-charts), the selection object assumes the "DrawingObjects" type, which when counted returns the correct number of selected objects. However when I try to access them via "Selection.Item(x)" I again can access all DrawingObjects in the worksheet. Is there anyway to determine which drawing objects are selected? And access them (so I can determine whether they are charts)?
Is there a way of determining the series that has been selected on a chart?
View 3 Replies View RelatedI have the following code which will determine if a user has selected a entire column.
If Selection.Rows.Count = 65536 Then
MsgBox "User has selected an entire column."
End If
Although it worked, but is there a better way to do it? I was thinking if future version of Excel goes beyond 65536 rows, then this code is definitely unusable!!
is there any way to get the value for the selected item from the combo box in vba code?
View 5 Replies View RelatedI have a column with comboboxes in each cell, each with LinkedCell set to the cell the combobox sits inside. Once the selection is made, I hide the combobox with .Visible="False". I want to be able to unhide hidden comboboxes by selecting a range of cells in the column, and then finding the corresponding comboboxes within each cell in this range, and unhide them.
View 3 Replies View RelatedI need to determine if any of the cells selected by the user have an indent level equal to 1 and if so then have them confirm the action. the below works well if the user only selects one cell but if the select 2 or more and any of the selected cells has an indent level greater than 1 it doesn't show the confirmation.
View 3 Replies View RelatedBasically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................
I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
View 2 Replies View RelatedI want to write a function that goes out and reads a cell, waits a few seconds, then reads the cell again, since it is constantly changing. Then report back the difference between the two readings of the same cell. The following code does not work since t0 and t1 always come back the same value.
Code:
Function ReadTime(t)
t0 = t
DoEvents
Application.wait Now() + TimeValue("00:00:15")
t1 = t
ReadTime = t1 - t0
End Function
I am working on a spreadsheet where individuals must enter a value between 1 - 4 or n/a. I would like to include a formula for each question where if they enter a value between 1-4, this scores 1 and if they enter n/a this scores 0. Does anyone know a simple formula I could use, I have tried IF formulas but keep getting muddled.
View 2 Replies View RelatedI have tried searching for a way for VBA to recognise in VBA if a cell contains any text, but most of my searches respond to specific cases for specific characters in a string
Basically, I'm just looking for the correct way to word a string of code that if it detects any text in column B (col 2 ) then it should not do anything, else it should cut the value in col 2 and paste it in col 8
I have a column (Column B) that contains some free text and I have another column (column K, rows 3 to 12) that contains a series of keywords.
What I would like to be able to do is if column B contains one of the keywords in column K then that keyword is placed in column C.
There will only ever be one keyword per row.
I'm trying to create a VBA module that will update data. However, I need it to skip any updates to data that has been set by the user. For simplicity, the user changes will be bold. I've found the info on the site for a function for counting cells in a range that are bold. But really just need to know how to get format of the cell.
View 4 Replies View RelatedColumn A contains a giant list of pathogens, with each often appearing in several ways (with/without species name, various misspellings, etc.) Column B contains truncated versions of maybe 20 or so pathogens that I'm interested in. What I want in column C is for an "X" or something to appear every time the code finds a pathogen of interest (that is, from column B) in the corresponding row of column A. For example:.............
View 4 Replies View Related[TABLE]
item01/11/200702/11/200703/11/2007
a1710
b2611
c359
[/TABLE]
I have the above table. I would like to determine which is the item that has Maximum value on a particular date. example on 2/11/2007, the maximum value is 7, so the result should be item a. How can I achieve this by using a formula.
I am trying to write an "IF" scenerio.
I want to be able to control what is put in a cell based what is placed in another cell.
For example.
I want to be able to only enter a credit/debit in B1 based by what I type in A1.
If any cell in column A is any of these numbers (41,47,48,49,50,51,52,60,61,65,68,63,67,69) then any number entered in a cell in column B can only be a positive number. If any cell in column A is any of these numbers (40,42,43,44,45,46,52,53,55,56,57,64,62,66) then any number entered in a cell in column B must be a negative number.
So if A1 is 41 then B1 must be positive (debit).
If A4 is 42 then B4 must be negative (credit).
Basically, I want a formula that searches for more than one word anywhere within a cell, and if the cell contains any of those words somewhere in it then I want to display a value, but if the words aren't there, then stay blank.
For example, my cell could say: "Magenta, Rose, Lime, Lavendar". I want to search this cell for any of the these words: "Lime", "Forest" or "Teal". If any of these appear, I then want the cell with this formula to say "Green".
I know the formula to search for only one word /phrase in a cell, I use: =(IF(ISNUMBER(SEARCH("yellow",A1)),"colour","-")), (i.e. if the word "yellow" appears anywhere in cell A1, then this cell will show the word "colour", if not then a "-".) I tried adding an 'OR' function in this to search multiple words but it didn't seem to work.
How do I determine a cell's type (e.g., numeric or string or formula) and if numeric, the format (e.g., currency, general, scientific) including the number of decimal places.
Is there a function that returns the object's properties? Must I use isnumeric, isformula, and so on or is there one function that returns this information?
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies View RelatedI have a large collection of datasets, sorted/grouped by rows. I need to perform some calculations against these SETS (Ave, Low, Median, etc.).
The number of member in each SET is variable (1 to NN), the number of SET Members is included in the data. Example:
Code:
3 Data Sets:
Row Set Members Price Ave
1 A 4 $1.00
2 A 4 $1.25
3 A 4 $1.50
4 A 4 $1.75
5 B 1 $4.00
6 C 3 $10.45
7 C 3 $14.50
8 C 3 $17.75
how to compute the AVE value for each SET above?
I am thinking if I can determine the FIRST Row number for each set, add the MemberQty as an ~offset to determine the LAST Row, then use an Indirect reference to compute the ave, something like:
=AVERAGE(INDIRECT("D"&A1&":D"&A1+C1)) The SET's Ave value should be added to EACH row in each SET.
I am struggling trying to point a formula like this to the First Row of the NEXT set.
Is there a way using a macro to determine which cells have a dropdown list and then retrieve the name?
View 4 Replies View RelatedIn VBA code, what's the simplest way to verify whether a cell is a member of a pivot table or not? I have a complicated way of doing this by checking if creating a formula in another cell with a reference to the cell in question generates the GETPIVOTDATA function; however, it seems like there should be a much simpler way.
View 3 Replies View RelatedI've searched the forumn but have found no resolution to my dilema. I'm trying to accomplish the following:
I would like to apply an Alternate number to a job that meets a certain criteria. The criteria is based on a Marker, which should be automatically determined based on the count of total jobs that are Estate or Custom. The Alternate number needs to be applied to certain % of the Jobs. What I've done is to get the count of jobs Estate or Custom then based on the count I have applied the Alternate % to determine the actual number of jobs that should have the marker applied. Here is where I need the help. The next step would be be to apply the marker to the number of jobs. I'm not sure how I can accomplish this task. Attached is an excel spreadsheet with an example of what I'm trying to achieve as well as what the end result should look like.
It's been awhile since I've done this...and I tried searching for the solution but to no avail.
I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:
Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
I have a spreadsheet where I need to check if one cell value is greater than 6 other cell values in the row, then have it enter 1 in another cell if so.
For example: C1=5 C2=2 C3=3 C4=2 C5=7 C6=0 C7=4
The largest value is in cell C5, so I would like C6 to show that it is by entering a 1 in there.
1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"
Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.
I have a for/next loop working through a date range. I want to skip a date at the 'next' code line.
1st loop Date = 1/1/2008
miss 2/1/2008
2nd loop Date = 3/1/2008
For Each Date In Rng
Sheet1.copy
Sheet2.copy
Next Date+2
I am trying to make a spreadsheet for bank reconiliation. I found this formula, =if( countif($B1:$B$1000,D1),D1)=0,D1,"") but there are two open parathese and three close. What needs to be corrected?
View 3 Replies View RelatedI have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
View 3 Replies View Related