Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 852
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to display a true or false value in the cell containing a check box Dave K Excel Programming 1 August 26th 10 06:25 PM
Check Box linked cell offset Tracey Excel Programming 1 February 8th 10 09:59 AM
check box linked cell ranswert Excel Programming 5 March 27th 08 12:41 AM
Check if Conditional Format is True or False / Check cell Color Kevin McCartney Excel Worksheet Functions 5 June 29th 07 11:12 AM
How to know the linked cell of a check box gm139[_2_] Excel Programming 3 February 5th 06 04:33 PM


All times are GMT +1. The time now is 01:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"