PDA

View Full Version : [SOLVED] Distinguishing Between FALSE and 0



Cyberdude
06-26-2005, 04:34 PM
I'm not sure why I'm struggling with this, but I just can't figure it out. I want to use an Inputbox to ask the user for a color index number. He also can reply by clicking Cancel.
I can't figure out whether he has replied with a zero (valid value) or pressed the Cancel button. Here's the basic code:


Sub SetTabColor()
Dim TabColor As Variant, Msg$
Msg = "Enter the sheet tab new color:"
TabColor = Application.InputBox(Msg, Title, Default:=0, Type:=5) 'Number or boolean
If TabColor = False Then GoTo Finish 'Cancel
' unrelated logic here
Finish:
End Sub

I've tried making TabColor an integer, but it didn't help.
I tried using a Type:= 1 instead of 5 (1 + 4). No help.
No matter what I do, the test for Cancel always succeeds, so it goes to Finish. What am I missing?? :banghead:

Bob Phillips
06-26-2005, 04:55 PM
I'm not sure why I'm struggling with this, but I just can't figure it out. I want to use an Inputbox to ask the user for a color index number. He also can reply by clicking Cancel.
I can't figure out whether he has replied with a zero (valid value) or pressed the Cancel button.

Hey Dude,

Test for "False"



Sub SetTabColor()
Dim TabColor As Variant, Msg$
Msg = "Enter the sheet tab new color:"
TabColor = Application.InputBox(Msg, Title, Default:=0, Type:=5) 'Number or boolean
If TabColor = "False" Then GoTo Finish 'Cancel
' unrelated logic here
Finish:
End Sub


But I must say this is a singularly unfriendly way of getting a colour. Here is a routine that is a tad friendlier



Sub SetTabColor()
Debug.Print GetColorindex
Finish:
End Sub


Function GetColorindex(Optional Text As Boolean = False) As Long
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColorindex = ActiveCell.Interior.ColorIndex
If GetColorindex = xlColorIndexAutomatic And Not Text Then
GetColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Function

Cyberdude
06-27-2005, 03:06 PM
Hey, xld . . . how you be?

Test for "False"
If you'll notice, my problem is that one of the valid values the user (me) can specify is zero, and in fact, it is the default value set in Inputbox. When I test for False, it works like a charm. Unfortunately, if I click OK or enter 0 manually, the test for False also is true. So for the value zero, the logic always transfers to the label Finish. That's not nice. No matter how I write it, it just can't seem to make the discrimination between False and zero. It's got to be something I'm not doing.
I finally changed Application.Inputbox to just Inputbox, because I know the two work a little differently. And it works to my satisfaction. So in that sense, I've solved the coding problem, but I still don't know what I should be doing with the Excel Inputbox, which I prefer usually.


But I must say this is a singularly unfriendly way of getting a colour. Here is a routine that is a tad friendlier
Sorry, xld, but I don't understand what your alternative coding is supposed to do for me. It appears that it determines what a colorindex value is. My code is designed to set a sheet tab color. Actually I want my routine eventually to set the color of 6 tabs, otherwise I could just click on the tab and change the color. Not being very conversent in VBA, I gotta admit my code is a tad simpleminded ofttimes, but it usually gets the job done. Except this time it doesn't. Pfft. It's a leaning tool if nothing else. Thanx for your suggestions.

P.S. xld you really ought to learn how to spell "color". :rotlaugh:

Bob Phillips
06-27-2005, 03:41 PM
If you'll notice, my problem is that one of the valid values the user (me) can specify is zero, and in fact, it is the default value set in Inputbox. When I test for False, it works like a charm. Unfortunately, if I click OK or enter 0 manually, the test for False also is true. So for the value zero, the logic always transfers to the label Finish. That's not nice.

I did understand your problem. In the code I gave, I tested the result against "False", not against "False". So, Cancel took that "False" path, but 0 diid not, so you could easily differntiate between them.


Sorry, xld, but I don't understand what your alternative coding is supposed to do for me. It appears that it determines what a colorindex value is. My code is designed to set a sheet tab color. Actually I want my routine eventually to set the color of 6 tabs, otherwise I could just click on the tab and change the color. Not being very conversent in VBA, I gotta admit my code is a tad simpleminded ofttimes, but it usually gets the job done. Except this time it doesn't. Pfft. It's a leaning tool if nothing else. Thanx for your suggestions.

My routine does return a colorindex, but it gives a nice, firendly (IMO) way of getting that coloriindex by throwing up a colour picker. So it is visual, as against your nasty :devil: inputbox where the user has to know what the colorindex numbers are. The returned colorindex would then be used to set the sheet colour. Something along the lines of



Sub SetTabColorv2()
Worksheets("Sheet1").Tab.ColorIndex = GetColorindex
Worksheets("Sheet2").Tab.ColorIndex = GetColorindex
Worksheets("Sheet3").Tab.ColorIndex = GetColorindex
End Sub


Function GetColorindex(Optional Text As Boolean = False) As Long
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColorindex = ActiveCell.Interior.ColorIndex
If GetColorindex = xlColorIndexAutomatic And Not Text Then
GetColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Function


This is similar to the way that you would set each individually through the tab, but is in VBA and thus continuous.


P.S. xld you really ought to learn how to spell "color".

This is what Anne says to me, but as I tell her, I already know how to spell colour, just as I know that days come before months in a date, and that chips are a long rectangular tube of potato deep fried. and that momentarily means a short period of time not soon. It is called the English language for good reason :whip

Ken Puls
06-27-2005, 03:49 PM
This is what Anne says to me, but as I tell her, I already know how to spell colour, just as I know that days come before months in a date, and that chips are a long rectangular tube of potato deep fried. and that momentarily means a short period of time not soon. It is called the English language for good reason :whip

ROTFLMAO!

:thumb

Cyberdude
06-28-2005, 11:16 AM
xld, thanx for the explanation. I'll spend some time on that code to better understand it.


but it gives a nice, firendly (IMO) way of getting

Incidentally, that's a nice effect the way you fixed the IMO so that a reader can "cursor it" and find out what it means. How did you do that? Also will you give me the URL for the site with all the shorthands. I keep forgettiing to write it down.

And I want to mention I would have responded to your note, but yesterday the forum's editor (once again) wouldn't let me type anything into the box. That's happened to me several times before. :motz2:

Zack Barresse
06-28-2005, 11:39 AM
How did you do that? Also will you give me the URL for the site with all the shorthands.
Hey Sid, do you mean the new Glossary (http://www.vbaexpress.com/forum/glossary.php)?

Bob: LOL!!

Ken: LOuL!!

:rotlaugh:

Ken Puls
06-28-2005, 12:38 PM
And I want to mention I would have responded to your note, but yesterday the forum's editor (once again) wouldn't let me type anything into the box. That's happened to me several times before. :motz2:

Hi Sid,

This happens to me on occassion as well, but usually just hitting refresh brings the box back so you can type in it.

HTH,

Bob Phillips
06-28-2005, 05:53 PM
Incidentally, that's a nice effect the way you fixed the IMO so that a reader can "cursor it" and find out what it means.

I know, I am just all giving :rotlaugh:


Also will you give me the URL for the site with all the shorthands. I keep forgettiing to write it down.

There is the glossary here at vbax that has many, but for any others, see http://www.ucc.ie/cgi-bin/uncgi/acronym