Hi, I need to either
a) put a checkbox into a cell (which I don't think is possible) or
b) make the cell itself act as a click-able checkbox
The reason is that I want the checkboxes themselves to appear and/or
disappear as a result of a conditional "IF" statement selected elsewhere on
the spreadsheet.
Any ideas?
Thanks
Hi Austin,
Try this technique that I have posted previously of having a check column,
and monitoring it with a worksheet selection change event. Add your code as
needed.
Rather than use a checkbox, I suggest just using a check column. So if we
assume that the data is in A1:E100 (change to suit), clicking in column A
will do what you want with this code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Austin" <Austin@discussions.microsoft.com> wrote in message
news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
> Hi, I need to either
>
> a) put a checkbox into a cell (which I don't think is possible) or
> b) make the cell itself act as a click-able checkbox
>
> The reason is that I want the checkboxes themselves to appear and/or
> disappear as a result of a conditional "IF" statement selected elsewhere
on
> the spreadsheet.
>
> Any ideas?
>
> Thanks
>
>
Hi!
Very nice!
Just a little nit pick, though..........
My default file settings are font Arial size 10. Row height 12.75.
When I run this routine the "checkmark" is placed in the cell but the row
height automatically changes to 14.25.
Can you tweak the code so that the row height does not change?
Thanks
Biff
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
> Hi Austin,
>
> Try this technique that I have posted previously of having a check column,
> and monitoring it with a worksheet selection change event. Add your code
> as
> needed.
>
> Rather than use a checkbox, I suggest just using a check column. So if we
> assume that the data is in A1:E100 (change to suit), clicking in column A
> will do what you want with this code.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> On Error GoTo sub_exit
> If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
> With Target
> If .Value = "a" Then
> .Value = ""
> Else
> .Value = "a"
> .Font.Name = "Marlett"
> End If
> End With
> End If
> sub_exit:
> Application.EnableEvents = True
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Austin" <Austin@discussions.microsoft.com> wrote in message
> news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
>> Hi, I need to either
>>
>> a) put a checkbox into a cell (which I don't think is possible) or
>> b) make the cell itself act as a click-able checkbox
>>
>> The reason is that I want the checkboxes themselves to appear and/or
>> disappear as a result of a conditional "IF" statement selected elsewhere
> on
>> the spreadsheet.
>>
>> Any ideas?
>>
>> Thanks
>>
>>
>
>
P.S.
If I reduce the font size to say, 8, the row height does not change but then
the "checkmark" is too small.
Biff
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
> Hi Austin,
>
> Try this technique that I have posted previously of having a check column,
> and monitoring it with a worksheet selection change event. Add your code
> as
> needed.
>
> Rather than use a checkbox, I suggest just using a check column. So if we
> assume that the data is in A1:E100 (change to suit), clicking in column A
> will do what you want with this code.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> On Error GoTo sub_exit
> If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
> With Target
> If .Value = "a" Then
> .Value = ""
> Else
> .Value = "a"
> .Font.Name = "Marlett"
> End If
> End With
> End If
> sub_exit:
> Application.EnableEvents = True
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Austin" <Austin@discussions.microsoft.com> wrote in message
> news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
>> Hi, I need to either
>>
>> a) put a checkbox into a cell (which I don't think is possible) or
>> b) make the cell itself act as a click-able checkbox
>>
>> The reason is that I want the checkboxes themselves to appear and/or
>> disappear as a result of a conditional "IF" statement selected elsewhere
> on
>> the spreadsheet.
>>
>> Any ideas?
>>
>> Thanks
>>
>>
>
>
=IF(B1=1,"ü","û")
This formula outputs a checkmark if B1=1, otherwise it outputs a fancy x
(similar to the checkmark). You will need to change the font of the cell to
windings. Something similar might work for you.
"Austin" wrote:
> Hi, I need to either
>
> a) put a checkbox into a cell (which I don't think is possible) or
> b) make the cell itself act as a click-able checkbox
>
> The reason is that I want the checkboxes themselves to appear and/or
> disappear as a result of a conditional "IF" statement selected elsewhere on
> the spreadsheet.
>
> Any ideas?
>
> Thanks
>
>
I'm sorry. Ignore my post. I am way off.
"Sloth" wrote:
> =IF(B1=1,"ü","û")
>
> This formula outputs a checkmark if B1=1, otherwise it outputs a fancy x
> (similar to the checkmark). You will need to change the font of the cell to
> windings. Something similar might work for you.
>
> "Austin" wrote:
>
> > Hi, I need to either
> >
> > a) put a checkbox into a cell (which I don't think is possible) or
> > b) make the cell itself act as a click-able checkbox
> >
> > The reason is that I want the checkboxes themselves to appear and/or
> > disappear as a result of a conditional "IF" statement selected elsewhere on
> > the spreadsheet.
> >
> > Any ideas?
> >
> > Thanks
> >
> >
Look at what it was before the change, do the change and then set it back:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myHeight As Double
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
myHeight = .EntireRow.RowHeight
.Value = "a"
.Font.Name = "Marlett"
.EntireRow.RowHeight = myHeight
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub
(I changed the font size to 8 (80% of 10) and it didn't look very nice.)
Biff wrote:
>
> Hi!
>
> Very nice!
>
> Just a little nit pick, though..........
>
> My default file settings are font Arial size 10. Row height 12.75.
>
> When I run this routine the "checkmark" is placed in the cell but the row
> height automatically changes to 14.25.
>
> Can you tweak the code so that the row height does not change?
>
> Thanks
>
> Biff
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
> > Hi Austin,
> >
> > Try this technique that I have posted previously of having a check column,
> > and monitoring it with a worksheet selection change event. Add your code
> > as
> > needed.
> >
> > Rather than use a checkbox, I suggest just using a check column. So if we
> > assume that the data is in A1:E100 (change to suit), clicking in column A
> > will do what you want with this code.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Application.EnableEvents = False
> > On Error GoTo sub_exit
> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
> > With Target
> > If .Value = "a" Then
> > .Value = ""
> > Else
> > .Value = "a"
> > .Font.Name = "Marlett"
> > End If
> > End With
> > End If
> > sub_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Austin" <Austin@discussions.microsoft.com> wrote in message
> > news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
> >> Hi, I need to either
> >>
> >> a) put a checkbox into a cell (which I don't think is possible) or
> >> b) make the cell itself act as a click-able checkbox
> >>
> >> The reason is that I want the checkboxes themselves to appear and/or
> >> disappear as a result of a conditional "IF" statement selected elsewhere
> > on
> >> the spreadsheet.
> >>
> >> Any ideas?
> >>
> >> Thanks
> >>
> >>
> >
> >
--
Dave Peterson
That works.
Thanks, Dave!
Biff
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:437CDF96.C6837972@verizonXSPAM.net...
> Look at what it was before the change, do the change and then set it back:
>
> Option Explicit
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim myHeight As Double
> Application.EnableEvents = False
> On Error GoTo sub_exit
> If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
> With Target
> If .Value = "a" Then
> .Value = ""
> Else
> myHeight = .EntireRow.RowHeight
> .Value = "a"
> .Font.Name = "Marlett"
> .EntireRow.RowHeight = myHeight
> End If
> End With
> End If
> sub_exit:
> Application.EnableEvents = True
> End Sub
>
> (I changed the font size to 8 (80% of 10) and it didn't look very nice.)
>
> Biff wrote:
>>
>> Hi!
>>
>> Very nice!
>>
>> Just a little nit pick, though..........
>>
>> My default file settings are font Arial size 10. Row height 12.75.
>>
>> When I run this routine the "checkmark" is placed in the cell but the row
>> height automatically changes to 14.25.
>>
>> Can you tweak the code so that the row height does not change?
>>
>> Thanks
>>
>> Biff
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
>> > Hi Austin,
>> >
>> > Try this technique that I have posted previously of having a check
>> > column,
>> > and monitoring it with a worksheet selection change event. Add your
>> > code
>> > as
>> > needed.
>> >
>> > Rather than use a checkbox, I suggest just using a check column. So if
>> > we
>> > assume that the data is in A1:E100 (change to suit), clicking in column
>> > A
>> > will do what you want with this code.
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > Application.EnableEvents = False
>> > On Error GoTo sub_exit
>> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>> > With Target
>> > If .Value = "a" Then
>> > .Value = ""
>> > Else
>> > .Value = "a"
>> > .Font.Name = "Marlett"
>> > End If
>> > End With
>> > End If
>> > sub_exit:
>> > Application.EnableEvents = True
>> > End Sub
>> >
>> >
>> > 'This is worksheet event code, which means that it needs to be
>> > 'placed in the appropriate worksheet code module, not a standard
>> > 'code module. To do this, right-click on the sheet tab, select
>> > 'the View Code option from the menu, and paste the code in.
>> >
>> >
>> >
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Austin" <Austin@discussions.microsoft.com> wrote in message
>> > news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
>> >> Hi, I need to either
>> >>
>> >> a) put a checkbox into a cell (which I don't think is possible) or
>> >> b) make the cell itself act as a click-able checkbox
>> >>
>> >> The reason is that I want the checkboxes themselves to appear and/or
>> >> disappear as a result of a conditional "IF" statement selected
>> >> elsewhere
>> > on
>> >> the spreadsheet.
>> >>
>> >> Any ideas?
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>
> --
>
> Dave Peterson
Thanks, Bob!
Biff
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:wp1faY76FHA.2888@tk2msftngp13.phx.gbl...
> Good point Biff. I'll add that to my archive.
>
> Thanks
>
> Bob
>
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:OAJDEJ76FHA.2012@TK2MSFTNGP14.phx.gbl...
>> That works.
>>
>> Thanks, Dave!
>>
>> Biff
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:437CDF96.C6837972@verizonXSPAM.net...
>> > Look at what it was before the change, do the change and then set it
> back:
>> >
>> > Option Explicit
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > Dim myHeight As Double
>> > Application.EnableEvents = False
>> > On Error GoTo sub_exit
>> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>> > With Target
>> > If .Value = "a" Then
>> > .Value = ""
>> > Else
>> > myHeight = .EntireRow.RowHeight
>> > .Value = "a"
>> > .Font.Name = "Marlett"
>> > .EntireRow.RowHeight = myHeight
>> > End If
>> > End With
>> > End If
>> > sub_exit:
>> > Application.EnableEvents = True
>> > End Sub
>> >
>> > (I changed the font size to 8 (80% of 10) and it didn't look very
>> > nice.)
>> >
>> > Biff wrote:
>> >>
>> >> Hi!
>> >>
>> >> Very nice!
>> >>
>> >> Just a little nit pick, though..........
>> >>
>> >> My default file settings are font Arial size 10. Row height 12.75.
>> >>
>> >> When I run this routine the "checkmark" is placed in the cell but the
> row
>> >> height automatically changes to 14.25.
>> >>
>> >> Can you tweak the code so that the row height does not change?
>> >>
>> >> Thanks
>> >>
>> >> Biff
>> >>
>> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> >> news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
>> >> > Hi Austin,
>> >> >
>> >> > Try this technique that I have posted previously of having a check
>> >> > column,
>> >> > and monitoring it with a worksheet selection change event. Add your
>> >> > code
>> >> > as
>> >> > needed.
>> >> >
>> >> > Rather than use a checkbox, I suggest just using a check column. So
> if
>> >> > we
>> >> > assume that the data is in A1:E100 (change to suit), clicking in
> column
>> >> > A
>> >> > will do what you want with this code.
>> >> >
>> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> >> > Application.EnableEvents = False
>> >> > On Error GoTo sub_exit
>> >> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>> >> > With Target
>> >> > If .Value = "a" Then
>> >> > .Value = ""
>> >> > Else
>> >> > .Value = "a"
>> >> > .Font.Name = "Marlett"
>> >> > End If
>> >> > End With
>> >> > End If
>> >> > sub_exit:
>> >> > Application.EnableEvents = True
>> >> > End Sub
>> >> >
>> >> >
>> >> > 'This is worksheet event code, which means that it needs to be
>> >> > 'placed in the appropriate worksheet code module, not a standard
>> >> > 'code module. To do this, right-click on the sheet tab, select
>> >> > 'the View Code option from the menu, and paste the code in.
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> > HTH
>> >> >
>> >> > RP
>> >> > (remove nothere from the email address if mailing direct)
>> >> >
>> >> >
>> >> > "Austin" <Austin@discussions.microsoft.com> wrote in message
>> >> > news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
>> >> >> Hi, I need to either
>> >> >>
>> >> >> a) put a checkbox into a cell (which I don't think is possible) or
>> >> >> b) make the cell itself act as a click-able checkbox
>> >> >>
>> >> >> The reason is that I want the checkboxes themselves to appear
>> >> >> and/or
>> >> >> disappear as a result of a conditional "IF" statement selected
>> >> >> elsewhere
>> >> > on
>> >> >> the spreadsheet.
>> >> >>
>> >> >> Any ideas?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>>
>>
>
>
Sorry about hijacking the thread!
To address the OP:
>The reason is that I want the checkboxes themselves to appear and/or
>disappear as a result of a conditional "IF" statement selected elsewhere on
>the spreadsheet.
The only way to do that if you use this "clickable cell" method (other than
more code) is to use conditional formatting to "hide" the checkmark. It
would still be there but would not be seen. Is that something you want to
pursue?
Biff
"Biff" <biffinpitt@comcast.net> wrote in message
news:eE$Gzx76FHA.3048@TK2MSFTNGP10.phx.gbl...
> Thanks, Bob!
>
> Biff
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:wp1faY76FHA.2888@tk2msftngp13.phx.gbl...
>> Good point Biff. I'll add that to my archive.
>>
>> Thanks
>>
>> Bob
>>
>>
>> "Biff" <biffinpitt@comcast.net> wrote in message
>> news:OAJDEJ76FHA.2012@TK2MSFTNGP14.phx.gbl...
>>> That works.
>>>
>>> Thanks, Dave!
>>>
>>> Biff
>>>
>>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>>> news:437CDF96.C6837972@verizonXSPAM.net...
>>> > Look at what it was before the change, do the change and then set it
>> back:
>>> >
>>> > Option Explicit
>>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>> > Dim myHeight As Double
>>> > Application.EnableEvents = False
>>> > On Error GoTo sub_exit
>>> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>>> > With Target
>>> > If .Value = "a" Then
>>> > .Value = ""
>>> > Else
>>> > myHeight = .EntireRow.RowHeight
>>> > .Value = "a"
>>> > .Font.Name = "Marlett"
>>> > .EntireRow.RowHeight = myHeight
>>> > End If
>>> > End With
>>> > End If
>>> > sub_exit:
>>> > Application.EnableEvents = True
>>> > End Sub
>>> >
>>> > (I changed the font size to 8 (80% of 10) and it didn't look very
>>> > nice.)
>>> >
>>> > Biff wrote:
>>> >>
>>> >> Hi!
>>> >>
>>> >> Very nice!
>>> >>
>>> >> Just a little nit pick, though..........
>>> >>
>>> >> My default file settings are font Arial size 10. Row height 12.75.
>>> >>
>>> >> When I run this routine the "checkmark" is placed in the cell but the
>> row
>>> >> height automatically changes to 14.25.
>>> >>
>>> >> Can you tweak the code so that the row height does not change?
>>> >>
>>> >> Thanks
>>> >>
>>> >> Biff
>>> >>
>>> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>>> >> news:uwQ6Mv66FHA.3760@TK2MSFTNGP14.phx.gbl...
>>> >> > Hi Austin,
>>> >> >
>>> >> > Try this technique that I have posted previously of having a check
>>> >> > column,
>>> >> > and monitoring it with a worksheet selection change event. Add your
>>> >> > code
>>> >> > as
>>> >> > needed.
>>> >> >
>>> >> > Rather than use a checkbox, I suggest just using a check column. So
>> if
>>> >> > we
>>> >> > assume that the data is in A1:E100 (change to suit), clicking in
>> column
>>> >> > A
>>> >> > will do what you want with this code.
>>> >> >
>>> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>> >> > Application.EnableEvents = False
>>> >> > On Error GoTo sub_exit
>>> >> > If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>>> >> > With Target
>>> >> > If .Value = "a" Then
>>> >> > .Value = ""
>>> >> > Else
>>> >> > .Value = "a"
>>> >> > .Font.Name = "Marlett"
>>> >> > End If
>>> >> > End With
>>> >> > End If
>>> >> > sub_exit:
>>> >> > Application.EnableEvents = True
>>> >> > End Sub
>>> >> >
>>> >> >
>>> >> > 'This is worksheet event code, which means that it needs to be
>>> >> > 'placed in the appropriate worksheet code module, not a standard
>>> >> > 'code module. To do this, right-click on the sheet tab, select
>>> >> > 'the View Code option from the menu, and paste the code in.
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >> > --
>>> >> >
>>> >> > HTH
>>> >> >
>>> >> > RP
>>> >> > (remove nothere from the email address if mailing direct)
>>> >> >
>>> >> >
>>> >> > "Austin" <Austin@discussions.microsoft.com> wrote in message
>>> >> > news:97DCE94A-68C4-4F91-A4F7-8F1A014DEA2C@microsoft.com...
>>> >> >> Hi, I need to either
>>> >> >>
>>> >> >> a) put a checkbox into a cell (which I don't think is possible) or
>>> >> >> b) make the cell itself act as a click-able checkbox
>>> >> >>
>>> >> >> The reason is that I want the checkboxes themselves to appear
>>> >> >> and/or
>>> >> >> disappear as a result of a conditional "IF" statement selected
>>> >> >> elsewhere
>>> >> > on
>>> >> >> the spreadsheet.
>>> >> >>
>>> >> >> Any ideas?
>>> >> >>
>>> >> >> Thanks
>>> >> >>
>>> >> >>
>>> >> >
>>> >> >
>>> >
>>> > --
>>> >
>>> > Dave Peterson
>>>
>>>
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks