Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
I have an array of formulas. I wanted to enable manual user input on these array but still preserve the formula. So I added a checkbox. (DefaultOpt). If the box is checked the formula is restored into the cells in the array. The following code seems to work. (But suggestion for any improvement is welcome. I am sort of green at Excel/VBA) ---- Private Sub defaultOpt_Click() For i = 15 To 28 ActiveWorkbook.Worksheets("Model 2").Cells(i, 4).Formula = "=FORMULA HERE" end sub ---- However, I also want to make sure that, if the value of a cell in the array is changed, the box should become unchecked. Any suggestion how I proceed to do this? Thanks in advance, pac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
pac,
In the worksheet's codemodule, use this: Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize(14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub HTH, Bernie MS Excel MVP "packat" wrote in message ... I have an array of formulas. I wanted to enable manual user input on these array but still preserve the formula. So I added a checkbox. (DefaultOpt). If the box is checked the formula is restored into the cells in the array. The following code seems to work. (But suggestion for any improvement is welcome. I am sort of green at Excel/VBA) ---- Private Sub defaultOpt_Click() For i = 15 To 28 ActiveWorkbook.Worksheets("Model 2").Cells(i, 4).Formula = "=FORMULA HERE" end sub ---- However, I also want to make sure that, if the value of a cell in the array is changed, the box should become unchecked. Any suggestion how I proceed to do this? Thanks in advance, pac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
-----Original Message----- pac, In the worksheet's codemodule, use this: Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Thanks for the response. It works to some extent. Still some problem (design problem really). When the check box is clicked, the formula is reinstated, and as a result, some of the values oif the cells in the range was update. This causes a collision between the two Sub's. I need to add another if condition in the second Sub to avoid changes made by the formula itself. Any diea? Thanks much, pac Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub HTH, Bernie MS Excel MVP "packat" wrote in message ... I have an array of formulas. I wanted to enable manual user input on these array but still preserve the formula. So I added a checkbox. (DefaultOpt). If the box is checked the formula is restored into the cells in the array. The following code seems to work. (But suggestion for any improvement is welcome. I am sort of green at Excel/VBA) ---- Private Sub defaultOpt_Click() For i = 15 To 28 ActiveWorkbook.Worksheets("Model 2").Cells(i, 4).Formula = "=FORMULA HERE" end sub ---- However, I also want to make sure that, if the value of a cell in the array is changed, the box should become unchecked. Any suggestion how I proceed to do this? Thanks in advance, pac . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
pac,
Thanks for the response. It works to some extent. Still some problem (design problem really). When the check box is clicked, the formula is reinstated Isn't that what you want? and as a result, some of the values oif the cells in the range was update. This causes a collision between the two Sub's. I need to add another if condition in the second Sub to avoid changes made by the formula itself. Any diea? Not really. You'll need to post a very clear example of what you mean. Bernie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
-----Original Message----- pac, Thanks for the response. It works to some extent. Still some problem (design problem really). When the check box is clicked, the formula is reinstated Isn't that what you want? Sorry for being unclear. The problem is when I check the check box, first defaultOpt kicks in and change the cell back to formula. But... The result of this change will trigger the Worksheet_Change procedure, which switch the checkbox back to uncheck. The question is, how do we modify Worksheet_Change to ignore the change made by defaultOpt procedure. Thanks, pac P.S I paste the two procedures back for your convenience. --------------------- Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub ----------------- - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
The question is, how do we modify Worksheet_Change to
ignore the change made by defaultOpt procedure. Thanks, pac P.S I paste the two procedures back for your convenience. --------------------- Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub ----------------- I guess I wanted to add a check statement if the content of the cell is the same as the formula. But I am not sure if the syntax is correct. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then If Cells(15,4).values Like "=FORMULA*" Then exit Sheet1.defaultOpt.Value = False End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
pac,
I thought that was taken care of by the cells.count1 line... Anyway, use Application.EnableEvents = False 'other code Application.EnableEvents = True Private Sub defaultOpt_Click() Application.EnableEvents = False ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize(14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Application.EnableEvents = False Sheet1.defaultOpt.Value = False Application.EnableEvents = True End If End Sub HTH, Bernie MS Excel MVP wrote in message ... -----Original Message----- pac, Thanks for the response. It works to some extent. Still some problem (design problem really). When the check box is clicked, the formula is reinstated Isn't that what you want? Sorry for being unclear. The problem is when I check the check box, first defaultOpt kicks in and change the cell back to formula. But... The result of this change will trigger the Worksheet_Change procedure, which switch the checkbox back to uncheck. The question is, how do we modify Worksheet_Change to ignore the change made by defaultOpt procedure. Thanks, pac P.S I paste the two procedures back for your convenience. --------------------- Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub ----------------- - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
pac,
This requires the use of undo: I will post the code tomorrow when I have time. Bernie wrote in message ... The question is, how do we modify Worksheet_Change to ignore the change made by defaultOpt procedure. Thanks, pac P.S I paste the two procedures back for your convenience. --------------------- Private Sub defaultOpt_Click() ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _ "=Actual FORMULA HERE, written for cell D15" End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then Sheet1.defaultOpt.Value = False End If End Sub ----------------- I guess I wanted to add a check statement if the content of the cell is the same as the formula. But I am not sure if the syntax is correct. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then If Cells(15,4).values Like "=FORMULA*" Then exit Sheet1.defaultOpt.Value = False End If End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
This may not work for you.
But I use a couple of columns. Say column A contains the question/description/whatever. Then column B could contain the formula (call it the calculated default answer) Column C would contain the manual override value Then Column D would contain the value to be used. =if(c2<"",c2,b2) And all subsequent formulas would point at the values in column D. If you find seeing column D irritating, you could hide it. With the worksheet protected and the formula cells locked (and the manual input columns unlocked), it may ever work! packat wrote: I have an array of formulas. I wanted to enable manual user input on these array but still preserve the formula. So I added a checkbox. (DefaultOpt). If the box is checked the formula is restored into the cells in the array. The following code seems to work. (But suggestion for any improvement is welcome. I am sort of green at Excel/VBA) ---- Private Sub defaultOpt_Click() For i = 15 To 28 ActiveWorkbook.Worksheets("Model 2").Cells(i, 4).Formula = "=FORMULA HERE" end sub ---- However, I also want to make sure that, if the value of a cell in the array is changed, the box should become unchecked. Any suggestion how I proceed to do this? Thanks in advance, pac -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use checkbox to toggle between formula and manual input
-----Original Message----- pac, .... Anyway, use Application.EnableEvents = False 'other code Application.EnableEvents = True Beautiful! Thanks Bernie. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual Input/Calculation in Same Cell | Excel Discussion (Misc queries) | |||
Cell Reference VS. Manual Input? | Excel Worksheet Functions | |||
Can't toggle (or display) formula bar | Excel Discussion (Misc queries) | |||
Manual Input During Macro Execution | Excel Programming | |||
Toggle Control & Formula | Excel Programming |