Remembering / Passing Double Clicked Cell?
Jul 26, 2012
how to remember or pass the place where the user doubleclicks. I am trying to make a form show up when a certain range is chosen. For now this range is static, but it will be dynamic in the future. Once the user double clicksa cell in this range, the form pops up.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("M4:AF23")) Is Nothing Then
Cancel = True
[Code]....
View 3 Replies
ADVERTISEMENT
Jun 4, 2014
I have a workbook with two sheets, Project Overview and Projects Detailed. In Project Overview column K is different project numbers and what i need the code to do is when this number is pressed it has to go to Projects Detailed and search for this number in column AV and hide the lines that does not correspond with the number that has been double clicked.
In project overview you should only be able to press the numbers from row 9 and down and in Projects Detailed the numbers in column AV also starts in row 9. Some of the cells in column AV are empty and these should also be hidden.
View 2 Replies
View Related
Jan 10, 2007
What in the world did I do now? When I click on any cell, the cursor turns into a thick "+" . Then, when I move my mouse up and down the spreadsheet, all the cells the "+" touches become highlighted.
View 9 Replies
View Related
Jul 31, 2006
I have a workbook with 3 listboxes: Listbox1, Listbox2 and Listbox3. Selecting an item in Listbox1 will query a database which populates a hidden worksheet. Listbox2 then populates itself by drawing on entries in the hidden worksheet. Selecting an item in Listbox2 populates Listbox3 in the same way.
At the moment I have the spreadsheet so that nothing is cleared when I close or open the workbook, allowing the listboxes to remember what they were populated with when I closed the workbook. However, the listboxes will not remember what was selected when I closed it. Is this something which I can easily fix with a listbox property? If not, what is the easiest way for each listbox to remember what was selected when I closed it?
View 6 Replies
View Related
Jul 31, 2006
I am using a userform that has several combo boxes, from which the user can select the required data. The following code sets up the userform: (Note: Some of the variables are public)
Private Sub UserForm_Initialize()
Dim ctrl As Control
Dim TableNo As Integer
Dim Cell As Integer
Dim RowScan As Long
For Each ctrl In Me.Controls
ctrl.Font.Size = 8
Next ctrl
NewEq = 0
TableNo = 0
Cabs.cboCode.AddItem "A"
Cabs.cboCode.AddItem "B"
Cabs.cboCode.AddItem "C"
Cabs.cboCode.AddItem "D"
Cabs.cboCode.AddItem "E".............................
View 4 Replies
View Related
Jul 16, 2014
When I am in my workbook and I click on a cell in a worksheet I would like to be able to have the row highlight when I click a cell, instead of trying to manually find it which at times I get lost with all the data on these sheets. Now my workbook is completed and all my formulas are done. Just trying to fine tune my databases with being able to do the "click this cell and the row highlights where I need to be" If this is possible I would like to do this for all 3 databases.
View 14 Replies
View Related
Jan 19, 2008
Is it possible to modify the following codes so that if the active cell is formatted as Date when the cell is clicked the pop-up calendar shows.
View 11 Replies
View Related
Oct 2, 2009
I need some VBA code which will insert a value into the cell when clicked.
First click = Value: 1
Second Click = Value: 2
Third Click = Value: "" (BLANK)
Cycle back to first click.
View 4 Replies
View Related
Feb 23, 2007
I've seen a spreadsheet where the user can click on a cell, and the row the cell is in automatically highlights.
This is useful where there is a lot of columns, and it also looks pretty snazzy!
How do I do this? I know one way is to put the following code in when you right click the sheet tab and choose "view code":
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
UsedRange.EntireRow.Interior.ColorIndex = xlNone
ActiveCell.EntireRow.Interior.ColorIndex = 6
End Sub
But then I can't have nice background colours for other cells. Any suggestions?
btw, I've v. new to VBA - best to assume I know nothing!
View 9 Replies
View Related
Apr 26, 2007
The idea is that once macro is started it will add the value of any clicked cell into the formula of the original cell.
ie.
1.start macro
2.click cell a5 (value=36)
3.type '+'
4.click cell b7 (value=21)
5.click enter to end macro
Result will be '=36+21' in the formula bar. The cell will then show the answer 57.
View 9 Replies
View Related
May 17, 2014
I have 5 columns in excel and a value in each column, i.e. J23, L23, N23, P23, R23 (I have left columns blank in between each one).
The values in J23, L23, N23, P23, R23 are all different.
I'd like to place a Checkbox underneath each of these cells (each checkbox being specifically for the value in the cell above it) so that when I click one of the checkboxes, the value that is linked to that checkbox is then copied into another sell, for example Cell J31.
View 9 Replies
View Related
Jul 29, 2008
There was a similar question I found answered in the archives. However the solution given there (using ActiveSheet.Buttons(Application.Caller).TopLeftCell.xxx) does not work in my case.
A little googling and I think the reason is that I added my buttons from the toolbar and not from the forms editor. My buttons are in the OLEObjects collection, not in Buttons.
So how do I determine which of my OLEObjects was clicked? Is there something similar to "Application.Caller"?
View 9 Replies
View Related
May 26, 2006
I've found a great thread that answered part of my problem (Change colour of text on update of cell)
This code changes the colour of a cell when selected. With the application I'm trying to make I need this effect to only occur in a certain area say A10:D30
I'm trying to make a scheduling page that uses timebars to represent when people are working. I though if a user could click or highlight the hours and a time bar produced I could lookup to see what hour the timebar starts and ends and show the hours worked. I also need it to cycle through 5 different colours before returning to white.
Private Sub worksheet_change(ByVal target As Range)
target.Interior.ColorIndex = 4
End Sub
View 9 Replies
View Related
Dec 27, 2009
I have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.
Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.
View 14 Replies
View Related
Sep 3, 2009
i am working on a spreadsheet that is doing two things 1) its allowing the user to choose between 1 to 3 number or N/A from the list validation option and 2) there is a checkbox if one selects that all the columns that have the formula will become N/A irrespect of data in it. I am using the following formula in the list validation option a3 is the check boc.
E5 =1
E6= 2
E7 = 3
E8=N/A
=if(a3=true, $E$8, $E$5:$E$8)
The issue I am having with this is, if I start selecting something from the drop down and half way I realize I need to check the box, it will only make it E8 value for the cells I have not touched and the ones I have already selected the drop down value it will remain. However I want it to override the value to E8 irrespect.
1) If the user of the spreadsheet clicks on the check box (this is in A3 cell) which i have linked it using format control then all the cell (Column E) that have the formula =if(A3=True, "N/A", ) will have N/A which is not applicable and not an error as you mentioned. I have successfully implemented this
2) If the user does not select the check box (a3) then each of the cell (Column E) has a drop down which the user can select either 1,2,3 or N/A. Which I have successfully implemented by using the data validations. Now the struggle I am having is as follows: I have attached the spreadsheet for your reference too.
Now if I select number 2 in cell E10, 2 in cell E11 and 3 in cell E 13 and you can see alll the other cell in column E are 0, because of the formula and the box in A3 is not checked in Tab 1. In Tab 2 I have checked the box everything turned N/A in column E except cells E10, E11 and E13 because I had previously selected 2,2,3 respectively. This is not what I want, what I want is when I check that box everything should turn to N/A basically an override function is what I want to created.
View 3 Replies
View Related
Jul 31, 2008
I have a sheet where it has got hyperlinks to many cells. When I click on the Hyperlink, the destination cell selected should start blinking.
View 9 Replies
View Related
Jan 29, 2008
I have a spreadsheet with contact information in it. I am trying to figure out a way to count how many times I click a contacts email address, then tally it in a weekly log. The end result will be a page that totals how many emails I sent each week for the year based on the number of clicks I have made to the spreadsheet.
Ideally I would also like to log the number of times the phone number is clicked too, but I am unsure how to make the phone number a live cell.
View 3 Replies
View Related
Aug 18, 2009
I attached an excel that I created. But I have one problem with it.
View 4 Replies
View Related
Mar 18, 2014
I want to click on a cell and automatically hide the 3 rows underneath where I clicked. Then unhide them when clicked again.
I will assign the vba code to the specific cells of the sheet where I need it. But it will always hide/unhide the 3 rows underneath the click.
View 2 Replies
View Related
Mar 2, 2009
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
View 9 Replies
View Related
Apr 20, 2013
I have a spreadsheet with near 300 tabs, each with a picture in the tab. The main tab has a list of all other tabs, the goal is to allow the user to click on a cell next to an entry, and have Excel flash the referenced tab to allow the user to see what the entry is referencing. I have written a simple macro that activates a desired tab, unhides it, displays a message box to pause the macro, rehides the tab, then returns the user to the main tab.
Rather than creating a macro for all 300 tabs and creating buttons I would love to use the Private Sub Worksheet_SelectionChange(ByBal Target As Range) or some variation thereof, to make my life much easier. The name of the tab is in cell A2, so I would want to have the user click on cell A1, activate the macro, then take A1 to A2 with something like A1 = A(x+1)->A2, then display the tab listed in A2. So rather than have 300 macros with Sheets("XYZ").Visible = True, I would love it to read Sheets(contents of referenced cell).Visible = True. with the contents of referenced cell coming from some manipulation of the cell I clicked on...
View 4 Replies
View Related
May 6, 2014
I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15")
.SlicerItems("BRUTE").Selected = True
End With
Would be best if this code ran on cell A1 click ...
View 1 Replies
View Related
Jun 6, 2014
I'm trying use a worksheet that contains named ranges as the source in a macro that will dynamically create pivot tables. The named range could be anything so it can't be hardcoded in the vba and has to come from the worksheet.
In my example, the string strI = "policy" which I know because the msgbox displays the string.
Code:
Dim strI As String
strI = Chr(34) & rngIn.Cells(1, 4).Value & Chr(34)
MsgBox strI
Application.Goto (strI)
But when I try to use strI in place of a hardcoded named range, it always gives me (Reference is not valid) error using the string variable in the following code but never when it's hardcoded in as "policy".
Code:
'This errors out
Application.Goto (strI)
'This works
Application.Goto ("policy")
View 2 Replies
View Related
May 30, 2014
code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.
For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.
View 1 Replies
View Related
Apr 8, 2008
I have a formula which is using named ranges-
=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)
where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38
The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)
Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.
I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.
The goal would be to have a couple of rows with the following that could be edited with new values
View 9 Replies
View Related
Oct 19, 2013
Not sure if possible but im trying to pass a range to a formula in excel from another cell.
[URL] .......
There is a sum formula in range B5 that sums the range (B9:13). I would like to have a formula in cell D5 that passes the new range in cells(D9:D13) to the formula in cell B5 and give the result in cell D5. Is this possible using Excel functions or would i need to create some sort of user defined function for this?
View 9 Replies
View Related
Nov 28, 2006
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
View 2 Replies
View Related
Apr 10, 2013
I have been searching for hours trying to find a solution to no avail. What I need to do is make sure that when I press a button on a form, the value in a combo-box should be populated as "text format" not as a number. The reason for this is that when my numbers start with a 0, the leading zero gets dropped and I can't run the rest of my code as the items don't match...
I have tried substituting the "Value" to "text" in : Me.cboItem.value and have tried for hours trying to name dim variables as a String and pass this into the cboItem...with no luck...
VB:
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Order")
'find first empty row in database
iRow = ws.cells.Find(What:="*", SearchOrder:=xlRows, _
[Code]...
The below populated the combo box cboItem:
VB:
Private Sub OptionButton1_Click()
With Me.cboItem
.Clear
.List = Application.Transpose(Worksheets("Item List").Range("List1"))
End With
End Sub
View 1 Replies
View Related
Sep 13, 2006
how to pass a cell content into a formula as a named range? For instance, I have 3 named range (Budget, Actual, Forecast) and I use the named range in vlookup formulas. But instead of using the named range in the formula directly, I want to refer to a cell where I can type in which named range I want to use. Can this be done? I am attaching a sample spreadsheet to clarify what I mean.
View 6 Replies
View Related
Mar 14, 2008
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
View 6 Replies
View Related
