Copying Value To Another Cell When Checkbox Clicked
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.
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.
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.
I have been using different keywords to find what I need. I have 3 checkboxes: cash, amex, other. I have a subtotal cell. When the appropriate checkbox is checked, the amount from the subtotal cell will auto go to the cell next to the checkbox. Is this VB?
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.
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!
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.
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"?
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)
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.
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
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.
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...
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.
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.
I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.
If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.
I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.
How can I stop my subs from jumping from one to another?
Code: Private Sub CheckBox18_Click() If CheckBox18.Value = True Then Worksheets("TRF").Rows("36:41").Hidden = False Worksheets("TRF").Rows("42:64").Hidden = True Worksheets("TRF").Rows("65:76").Hidden = True CheckBox19.Value = False
How can i hide and unhide one checkbox using another one? Can it be done using IF formula? And also i am using this checkbox to function something else as TRUE/FALSE.
I have this worksheet with 2 buttons and 2 textboxes.the first button is named search literally to search the items inputted in the textboxes and the second button is clear which clear all the inputted items in the textboxes and the search field. I manage to code those things however I have a main problem. How am I suppose to linked a checkbox with those items?
For example I searched this particular item so the checkbox corresponding to that item must show up in the left most column of it. and if I searched multiple items those checkboxes must show up too. I am having a hard time coding and trying since I am new in vba. I also attached a sample worksheet. I am using excel 2007.
I have drop-down list in cell C4 and inserted Checkbox next to it. Drop-down list has a blank default value. I want Checkbox to be ticked If value in C4 is not blank. My code so far is:
I am writing some VBA code in Excel 2007 and am stuck on a property with the standard form check box (not OLEObjects).
I would like to check and find out what the value is of the linked cell for a checkbox...
Like, if a user clicks the checkbox, it changes the linked cell value to true or false... But I cannot figure out how to "lookup" or "go and get" this true or false value through code...
I'm trying to achieve a search of a sheet using a selection in a listbox on my userform and then return a value of 'true or false' to my checkbox.
My code is below;
With Sheets("JobChecklist") Set r = .Columns("b").Find(Me.ListBox3.Text, , , xlWhole) If r Is Nothing Then
Exit Sub End If ff = r.Address: n = 0 Do n = n + 1 ReDim Preserve a(1 To 6, 1 To n) For i = 1 To 3 a(i, n) = r.Offset(0, 1).Value Next Set r = .Columns("b").FindNext(r) Loop Until ff = r.Address End With With Me.CheckBox30 If a.Value = True Then CheckBox30.Value = True Else CheckBox30.Value = False End If End With End Sub
I need a checkbox (actually a few but I will use the same code and change the name and cell reference) to do the following
Checkbox in A1 is ON by defaultB1 is 100User can free change the value of B1 when checkbox is onWhen checkbox is OFF"100" (or whatever number entered by the user) gets storedB1 changes to a relatively large number "9999"B1 changes cell color to grey and is locked for editWhen checkbox is ON againThe value stored previous to checkbox turned OFF is restored back to B1B1 changes back to normal cell color (no fill or white)B1 is unlocked and user can edit the cell
