Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Each time the check box is checked and linked cell D5 returns TRUE, what formula can I use to reduce the value in J5 by 1.
I tried using J1 as the begining value of say 10 but can't figure the formula in J5 to return 1 less each time D5 rings TRUE. Tried a Select Case in a change event macro but the range/target AND the "TRUE"/value I could not get to work. There will be several D cells with check box linked cells and the same amount of J values to be reduced as their respective D cell ring up a "TRUE". Howard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Each time the check box is checked and linked cell D5 returns TRUE,
what formula can I use to reduce the value in J5 by 1. I tried using J1 as the begining value of say 10 but can't figure the formula in J5 to return 1 less each time D5 rings TRUE. Tried a Select Case in a change event macro but the range/target AND the "TRUE"/value I could not get to work. There will be several D cells with check box linked cells and the same amount of J values to be reduced as their respective D cell ring up a "TRUE". Howard Did you try using a macro assigned to the checkboxes? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Howard Did you try using a macro assigned to the checkboxes? -- Garry Aha! I was able to make this work Sub CheckBox2_Click() If ThisWorkbook.Worksheets(1).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then Range("J5").Value = Range("J5").Value - 1 End If End Sub Thanks for hint. Howard |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Howard Did you try using a macro assigned to the checkboxes? -- Garry Aha! I was able to make this work Sub CheckBox2_Click() If ThisWorkbook.Worksheets(1).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then Range("J5").Value = Range("J5").Value - 1 End If End Sub Thanks for hint. Howard Congrats, but that approach requires each control to have its own sub. If you use an ActiveX control you can assign an index to its Tag property. Then all can use the same routine. You can also assign a GroupName to context-specific controls, for example, in different sections of a form. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Congrats, but that approach requires each control to have its own sub. If you use an ActiveX control you can assign an index to its Tag property. Then all can use the same routine. You can also assign a GroupName to context-specific controls, for example, in different sections of a form. -- Garry Hmmm, don't know if it is a Form or an ActiveX. My testing is with a Forms of course. I'll take a look at the ActiveX and see if I can 'ciffer what its Tag property is etc. Howard |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Oops.., forgot there's no Tag prop for this control!
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
On Thursday, March 12, 2015 at 11:00:27 PM UTC-7, GS wrote:
Oops.., forgot there's no Tag prop for this control! -- Garry Just poking about, I put an activex check box on the sheet. It came up numbered 22, don't know why as this is a new unused workbook...? Would have thought it would be 1. With the code here, it errors out on this line: Set cb = ActiveSheet.Shapes("Check Box 22") With "Name not found" Or with this as the name same as in the Property window, errors: Set cb = ActiveSheet.Shapes("CheckBox22") Does not support that Property or Method Private Sub CheckBox22_Click() Dim cb As Shape Set cb = ActiveSheet.Shapes("Check Box 22") If cb.OLEFormat.Object.Value = 1 Then MsgBox "Checkbox is Checked" Else MsgBox "Checkbox is not Checked" End If End Sub Now I remember why I use Forms.<g Howard |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Hi Howard,
Am Thu, 12 Mar 2015 23:46:35 -0700 (PDT) schrieb L. Howard: It came up numbered 22, don't know why as this is a new unused workbook...? Would have thought it would be 1. since Decembers patch day the index of ActiveX object starts with 21 But with last Tuesdays patches the index is correct again. Change the name to "CheckBox21" in your code if this CheckBox is the first in the sheet Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
ActiveX controls are objects of the sheet they're used on...
Sheet1 CheckBox1 ...refs as Me.CheckBox1 in the code module behind the sheet. By default, when you right-click the control in design mode you get... Private Sub CheckBox1_Click() End Sub ...in the code window. IOW, working with AX controls on a sheet is similar to working with the same control on a userform. There are a few differences like the worksheet version not having a Tag property, for example, whereas the userform version does. Forms controls are Shape objects, and so must be ref'd as such by their string name (or index in the shapes collection). -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Hi Howard,
Am Thu, 12 Mar 2015 23:46:35 -0700 (PDT) schrieb L. Howard: It came up numbered 22, don't know why as this is a new unused workbook...? Would have thought it would be 1. since Decembers patch day the index of ActiveX object starts with 21 But with last Tuesdays patches the index is correct again. Change the name to "CheckBox21" in your code if this CheckBox is the first in the sheet Regards Claus B. Why not "CheckBox1"? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Hi Garry,
Am Fri, 13 Mar 2015 03:09:28 -0400 schrieb GS: Why not "CheckBox1"? it seems that Howard has not installed the last updates and then his CheckBox is CheckBox21 But he can also change the name in the properties and then use the new name. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Hi Garry,
Am Fri, 13 Mar 2015 03:09:28 -0400 schrieb GS: Why not "CheckBox1"? it seems that Howard has not installed the last updates and then his CheckBox is CheckBox21 But he can also change the name in the properties and then use the new name. Regards Claus B. Hopefully, when renaming controls, a more meaningful one is assigned<g! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Here is another shot at it that errors on the Set cb = line... Renamed "Stuff" and shows "Stuff Checkbox" (no " "'s) in the Properties drop down at the very top of the Properties Window and in the Name box at the top of the properties list it shows as "Stuff" (no " "'s). (I set Caption to "Stuff It" (no " "'s) to let you know I am not confusing Caption with Name.) Both of these error out. Set cb = ActiveSheet.Shapes("Stuff CheckBox") Set cb = ActiveSheet.Shapes("Stuff") Private Sub Stuff_Click() Dim cb As Shape Set cb = ActiveSheet.Shapes("Stuff CheckBox") 'Set cb = ActiveSheet.Shapes("Stuff") If cb.OLEFormat.Object.Value = 1 Then MsgBox "Checkbox is Checked" Else MsgBox "Checkbox is not Checked" End If End Sub Could you please show on a linked workbook what a correct/workable example using this code and the name "My Stuff" for the activex checkbox? Howard |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Hi Howard,
Am Fri, 13 Mar 2015 01:45:10 -0700 (PDT) schrieb L. Howard: Renamed "Stuff" try: Private Sub Stuff_Click() Dim cb As Object Set cb = ActiveSheet.Stuff If cb = True Then MsgBox "Checkbox is Checked" Else MsgBox "Checkbox is not Checked" End If End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
Howard,
Just as "My Stuff" is not a legal, valid name for a control on userforms, neither is it for AX controls on sheets. Do not confuse the naming convention used for Forms controls with that of AX controls. Think of it this way... In the VBE code window: In the left side dropdown: General CheckBox1 Worksheet ..,where in a userform code window... In the left side dropdown: General CheckBox1 Userform ...where the concept of object hierarchy is identical. The AX control Name property is not a string, but rather the 'codename' of the object which can be used to ref the control in code. Because Forms controls are Shape objects, they are an entirely different species and handled same as any other Shape object. So in your code sample... Private Sub Stuff_Click() ' Handles code for an AX CheckBox control renamed "Stuff" ' Note that this name does not comply with any naming convention standard Dim cb As CheckBox Set cb = Me.Stuff Debug.Print cb.Caption End Sub ...where the result should be "Stuff It" according to the info you provide here. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
try:
Private Sub Stuff_Click() Dim cb As Object Set cb = ActiveSheet.Stuff If cb = True Then MsgBox "Checkbox is Checked" Else MsgBox "Checkbox is not Checked" End If End Sub Thanks Claus. Really pretty simple, just need the correct syntax. I got the code I posted from a google search and was working for the person there in that archived thread, or so they said. I'll just have to try and use them more to catch on I gurss. Thanks again. Howard Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check box linked cell = TRUE then J5 valued reduced by 1
On Friday, March 13, 2015 at 2:26:22 AM UTC-7, GS wrote:
Howard, Just as "My Stuff" is not a legal, valid name for a control on userforms, neither is it for AX controls on sheets. Do not confuse the naming convention used for Forms controls with that of AX controls. Think of it this way... In the VBE code window: In the left side dropdown: General CheckBox1 Worksheet .,where in a userform code window... In the left side dropdown: General CheckBox1 Userform ..where the concept of object hierarchy is identical. The AX control Name property is not a string, but rather the 'codename' of the object which can be used to ref the control in code. Because Forms controls are Shape objects, they are an entirely different species and handled same as any other Shape object. So in your code sample... Private Sub Stuff_Click() ' Handles code for an AX CheckBox control renamed "Stuff" ' Note that this name does not comply with any naming convention standard Dim cb As CheckBox Set cb = Me.Stuff Debug.Print cb.Caption End Sub ..where the result should be "Stuff It" according to the info you provide here. -- Garry Okay, thanks. A lot to understand with the activex controls. And a lot more can be done with them I assume. I'll keep plodding along as I can. Thanks, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to display a true or false value in the cell containing a check box | Excel Programming | |||
Check Box linked cell offset | Excel Programming | |||
check box linked cell | Excel Programming | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
How to know the linked cell of a check box | Excel Programming |