Distinguishing Between FALSE and 0

[Cyberdude]

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??

[Bob Phillips]

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]

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".

[Bob Phillips]

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 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

[Ken Puls]

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

ROTFLMAO!

[Cyberdude]

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.

[Zack Barresse]

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?

Bob: LOL!!

Ken: LOuL!!

[Ken Puls]

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.

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]

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

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