checkbox problem


B

bojan0810

Hi all!

Is it possible to hide checkbox based on value on cell to the left?

I mean that is possible, but what if cell to the left has formula in it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oShp As Shape
For Each oShp In Me.Shapes
If oShp.TopLeftCell.Address = Target.Offset(, 1).Address Then
oShp.Visible = (Target.Value <> "")
End If
Next
End Sub


This code works if cells are completly blank, but problem is I have formulas in cells. They are showing blank based on other criteria what isnt important now...

Anyway, is it possible to hide checkbox if cell formulas is showing blank?
 
Ad

Advertisements

G

GS

Hi all!
Is it possible to hide checkbox based on value on cell to the left?

I mean that is possible, but what if cell to the left has formula in
it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oShp As Shape
For Each oShp In Me.Shapes
If oShp.TopLeftCell.Address = Target.Offset(, 1).Address Then
oShp.Visible = (Target.Value <> "")
End If
Next
End Sub


This code works if cells are completly blank, but problem is I have
formulas in cells. They are showing blank based on other criteria
what isnt important now...

Anyway, is it possible to hide checkbox if cell formulas is showing
blank?
This implies your formula returns an empty string ("") from an IF
construct. If so try...

oShp.Visible = Not (Target.Value = "")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
B

bojan0810

Thanks for reply.

unfortunately it doesnt work. Its acting same as before.

Thing is, I just relize that. If I just "open" formula and hit enter, checkboxes are gone. But when formula add value, checkboxes doesnt show up.

And yeah it is if formula. It is actually some kind of survey made in excel. Basically if you choose different answer it will show different next question.
 
C

Claus Busch

Hi Bojan,

Am Thu, 8 May 2014 11:18:17 -0700 (PDT) schrieb (e-mail address removed):
Thing is, I just relize that. If I just "open" formula and hit enter, checkboxes are gone. But when formula add value, checkboxes doesnt show up.

And yeah it is if formula. It is actually some kind of survey made in excel. Basically if you choose different answer it will show different next question.
the changing of the value in the formula cell fires Worksheet_Calculate
but not Worksheet_change.
Refer in your code to the cell that changes the value in the formula
cell, the cell that is refered in the IF-Statement


Regards
Claus B.
 
B

bojan0810

https://dl.dropboxusercontent.com/u/57916703/sample2 (1).xlsm

Thanks for answering. I tried with that too and didnt work. I am got all are all are visible or all are empty.

This is what I am trying to do. This is original formula in it.

First 2 checkboxes are always show, because text in B column is always there.

Next if check one of the checkboxes it will populate next and based on that next I want to show checkboxes for that. And so on for rest of the sheet.
 
C

Claus Busch

Hi again,

Am Thu, 8 May 2014 21:15:56 +0200 schrieb Claus Busch:
For Each rngC In myRng
For Each oShp In Me.Shapes
If oShp.TopLeftCell.Address = rngC.Address Then
oShp.Visible = IIf(Target.Value <> "", False, True)
End If
Next
Next
change the above to:
For Each rngC In myRng
For Each oShp In Me.Shapes
If oShp.TopLeftCell.Address = rngC.Address Then
oShp.Visible = IIf(Target.Value <> "", True, False)
End If
Next
Next


Regards
Claus B.
 
Ad

Advertisements

B

bojan0810

I am not sure if I am doing something wrong...

I tried with that code and changed it as you said, but it is not doing anything actually. Do I need to do something with checkboxes too? Or something

Thanks
 
C

Claus Busch

Hi,

Am Thu, 8 May 2014 12:32:28 -0700 (PDT) schrieb (e-mail address removed):
I am not sure if I am doing something wrong...
sorry, it is my bad. I did not realise that the refered cells also have
formulas.
Try instead of WorkSheet_Change:

Private Sub Worksheet_Calculate()
Dim myRng As Range
Dim rngC As Range
Dim oShp As Shape

Set myRng = Range("C11:C12,C17:C20,C25:C28,C33:C34")
For Each rngC In myRng
For Each oShp In Me.Shapes
If oShp.TopLeftCell.Address = rngC.Offset(, 1).Address Then
oShp.Visible = IIf(Len(rngC.Value) > 0, True, False)
End If
Next
Next
End Sub

Or look here:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!326#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "CheckBoxes"


Regards
Claus B.
 
B

bojan0810

Dana Äetvrtak, 8. svibnja 2014. 21:50:51 UTC+2, korisnik Claus Busch napisao je:
Hi,



Am Thu, 8 May 2014 12:32:28 -0700 (PDT) schrieb (e-mail address removed):






sorry, it is my bad. I did not realise that the refered cells also have

formulas.

Try instead of WorkSheet_Change:



Private Sub Worksheet_Calculate()

Dim myRng As Range

Dim rngC As Range

Dim oShp As Shape



Set myRng = Range("C11:C12,C17:C20,C25:C28,C33:C34")

For Each rngC In myRng

For Each oShp In Me.Shapes

If oShp.TopLeftCell.Address = rngC.Offset(, 1).Address Then

oShp.Visible = IIf(Len(rngC.Value) > 0, True, False)

End If

Next

Next

End Sub



Or look here:

https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!326#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "CheckBoxes"





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
I downloaded yours but there were all missing boxes.

But I used this formula what you wrote and it worked like a charm.

Thank you very much for this. Now my nightmare is done lol.
 
B

bojan0810

Also I have one more question.

When I am "playing" with checkboxes and then exit without saving, after that when I try open same file it crashes, after few times trying then I finally open it with repairing mode.

If I save it again, next time works normal.

Do you know maybe why is that? This only happens with checkboxes, this happend even before macro code
 
G

GS

I remember doing a project some time ago where it involved a
questionaire set up like a 'wizard', so what showed when the user
clicked (what looked like a button) 'Next >' depended on the selections
made by the user. The project used lots of checkboxes and buttons and
so got rather wieldy trying to manage positions what with all the
hide/unhide going on with ranges. My soution was to rebuild the
questionaire same as I do with my own similar projects, wherein
checkboxes are actually cells that just look/act like checkboxes. Same
with the buttons. This particular questionaire had 17 sections to it,
and every section was a named range containing other named ranges, and
what displayed was dependant on what selections were made in 'all'
previous sections. This required helper columns to determine what
questions appeared, which contained formulas that had refs to previous
section results.

Major task getting the client to shift away from using controls on the
worksheet. Finally accepted, though, after running my sample redo.

This sort of approach uses the Worksheet_SelectionChange event to
monitor 'Target.Address' with a 'Select Case' construct to redirect
executable code. DataValidation was used for choice selections and
their respective code was managed in the Worksheet_Change event.

All in, the questionaire 'looked/acted' identical to the
'controls-on-sheet' version but was much easier to manage/maintain
simply using event handlers, cell formulas, and DV/CF functions for
choice/format config!

IMO you'll have way less problems going forward if you get rid of the
controls!<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
 
Ad

Advertisements

C

Claus Busch

Hi,

Am Thu, 8 May 2014 13:31:44 -0700 (PDT) schrieb (e-mail address removed):
When I am "playing" with checkboxes and then exit without saving, after that when I try open same file it crashes, after few times trying then I finally open it with repairing mode.
I cannot reproduce it for me. IF I close with or without saving I get no
error or crash. Do you have other macros in your workbook?
Have another look in OneDrive. I changed the code to reset all
checkboxes to false when opening the file.


Regards
Claus B.
 
B

bojan0810

Sorry for delay answer. Thanks for the file.

Thing is that sometimes it crashes when it has checkboxes in. It did before any code. Checkboxes are doing problems. I am using 2010 excel.

I didnt tried 2013 if they changed something or not.

Sometimes crashes then when I got to the file, it saying something about printer properties removed when repairing. After I save again, next time is ok.

I am thinking that there is some bug with checboxes.

This happened before too. I never found out why.

Its kinda weird because I dont save it at all and next time it crashes, before that it worked great.

Anyway, you really helped me alot. Thank you
 
C

Claus Busch

Hi,

Am Fri, 9 May 2014 09:19:27 -0700 (PDT) schrieb (e-mail address removed):
I didnt tried 2013 if they changed something or not.
I tested in xl2007
This happened before too. I never found out why.
what about Garrys suggestion to replace the checkboxes with Data
Validation => List => Yes, No?


Regards
Claus B.
 
B

bojan0810

Dana petak, 9. svibnja 2014. 18:31:33 UTC+2, korisnik Claus Busch napisao je:
Hi,



Am Fri, 9 May 2014 09:19:27 -0700 (PDT) schrieb (e-mail address removed):






I tested in xl2007






what about Garrys suggestion to replace the checkboxes with Data

Validation => List => Yes, No?





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Actually, Yes and No would be much better. Even for this it will need checkboxes because it is done already, but for other, and it will be more like that, thats for sure, I will use Yes, No...

Actually I never thought of that. It is much easier solution and I can use √ in data validation. And I dont need macro for hiding all.

It is very nice and easy solution.

Thanks for that too.
 
G

GS

Actually, Yes and No would be much better. Even for this it will need
checkboxes because it is done already, but for other, and it will be
more like that, thats for sure, I will use Yes, No...

Actually I never thought of that. It is much easier solution and I
can use √ in data validation. And I dont need macro for hiding all.

It is very nice and easy solution.

Thanks for that too.
The point of using the checkbox-like cells was a more efficient way to
deal with the 100s of selections possible on this questionaire! It's
much easier (and more user friendly) to double-click a checkbox cell
than click a DV down arrow, scroll the list if numerous choices, and
click their preference. Not saying this is a good thing for your
project if checkbox choices aren't suitable, though<g>.

How it handled options was...

Qs asked were dependant on earlier choices;

Options were listed in rows below the Q, and the choices were also
dependant on earlier choices relevant to the Q;

The DV dropdown was below the options rows. This list was also
dependant on the Q and its available options. So, for example, if
there were 3 options for a Q, 1 of which was disabled based on
earlier choices (say #2), then the DV list contained only 1 and 3.

Users had a choice, then, to use the DV list or enter directly
into its cell.

So, for example, if you've seen printed forms that ask a yes/no Q and
provide another field for 'If Yes then provide details...' or whatever.
Well on this form selecting Yes of double-clicking a checkbox
automajically displays (or hides) the next step.

<FWIW>
I started making forms like this in XL4.x! I started adding event code
features in XL2000. I'll be the 1st to admit it's daunting and can get
very wieldy, but you end up with some pretty pro-looking survey wizards
when it's all done.

This was also duplicated in a userform version so you could have a UI
choice of either sheet-based, form-based, or both! Either choice
displays a sheet-based 'summary' for reviewing which allowed going back
to to edit any particular section. Both also had a 'Navigator' panel
where you could 'jump' to any section. The sheet-based version uses a
DV list, the userform uses a listbox same as an options dialog would
have.

In some cases I've used a treeview when clients wanted to be able to
expand/colapse section nodes to show/hide subsections. I haven't done
this, though, since MS stopped including the "Microsoft Windows Common
Controls" in Windows starting with Vista because it meant having to
include the mscomctl.ocx with the project. The issue at the time was
that every app that uses them had to include them in their installers
or their apps wouldn't run! (Thanks MS!)

I'm back using them again having implemented a resolve conditional on
if the controls are missing on a users machine; my project registers
the controls at startup for use during runtime, then unregisters them
at shutdown. Otherwise, if another app fully installs the controls for
their use then my projects use those because they pre-exist at startup.
(This preserves my primary policy that my projects be 100% portable in
that they do not use the Registry and they cleanup after themselves at
shutdown so the host computer is not changed in any way!)

HTH...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ad

Advertisements

B

bojan0810

I made yesterday, that file with checkboxes, but now I made like you said. With dropdown.

Changed formula from TRUE to "√" with find and replace after hitingcrtl+`... Easiest way to replace formula.

Then used conditional formatting for cell where is √. For example, if cell to the left is populate it will fill this cell with color and whiteborder.

And made that cell to look like box. It worked like a charm.

Thanks again for that
 
G

GS

I made yesterday, that file with checkboxes, but now I made like you
said. With dropdown.

Changed formula from TRUE to "√" with find and replace after hiting
crtl+`... Easiest way to replace formula.

Then used conditional formatting for cell where is √. For example, if
cell to the left is populate it will fill this cell with color and
white border.

And made that cell to look like box. It worked like a charm.

Thanks again for that
Glad to help! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

Claus Busch

Hi,

Am Fri, 9 May 2014 10:03:40 -0700 (PDT) schrieb (e-mail address removed):
Actually I never thought of that. It is much easier solution and I can use ? in data validation. And I dont need macro for hiding all.
have another look:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!326#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "WithoutCheckboxes"

It looks a bit like checkboxes and works like checkboxes.
But it is a suggestion with macros.


Regards
Claus B.
 
Ad

Advertisements

B

bojan0810

Dana nedjelja, 11. svibnja 2014. 18:39:32 UTC+2, korisnik Claus Busch napisao je:
Hi,



Am Fri, 9 May 2014 10:03:40 -0700 (PDT) schrieb (e-mail address removed):






have another look:

https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!326#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "WithoutCheckboxes"



It looks a bit like checkboxes and works like checkboxes.

But it is a suggestion with macros.





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
This is very nice too. And it will always work, not like strugling with checkboxes on excel. I have really no idea why sometimes crashes with checkboxes. But this will always work, no crashing.
I tested it few times, even saved once to see. It always worked.
Other one with checkboxes it doesnt work always. I guess excel doesnt like checkboxes lol. But you can always trick excel to do same even it thinks it doesnt doing same lol.

Thanks for this too...

Anyway.

Without macros and checkboxes, I made like this.
https://dl.dropboxusercontent.com/u/57916703/withoutcheck.xlsx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problems with autofilter and checkboxes 11
Checkbox background pattern problem 1
Checkbox Problem on Locked Worksheet 5
Checkboxes 1
checkbox 1
CheckBox 1
Clear Checkboxes 4
hiding checkbox 1

Top