Select Case not seeing Null value

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi

I have a problem with code not 'recognising' a Null value in a combobox. The sheet involved is an input sheet, with several comboboxes for users to choose a value. In one box, depending on the value chosen, several lines on the sheet are hidden or unhidden and the value is then copied to another sheet.

Everything works fine until I click a 'Clear All Input' button. This allows users to clear all input cells and start again. I use a simple loop to reset all comboboxes
Code:
For Each obj In ActiveSheet.OLEObjects
    If obj.progID = "Forms.ComboBox.1" Then
        obj.Object.Value = ""
    End If
Next obj
The rest of the routine just clears the input cells.

When the clear routine runs, the combobox value is reset and appears as Null - but I cannot seem to trap this value in the combobox change code
Code:
Private Sub ComboBox8_Change()
Sheets("VRCM").Unprotect

Select Case ComboBox8.Value
    Case Is = "D 007":
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
     Case Is = "M 001":
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
    Case Is = "":
        Sheets("VRCM").Range("BE8").ClearContents
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
    Case Is = Null:
       Sheets("VRCM").Range("BE8").ClearContents
       If Rows("206:211").Hidden = False Then
           Rows("206:211").Hidden = True
       End If
    Case Else
       If Rows("206:211").Hidden = True Then
           Rows("206:211").Hidden = False
       End If
End Select
    
Sheets("VRCM").Protect
End Sub

When the clear routine runs, I need to ensure that rows 206 to 211 are hidden. All other values are trapped and the rows hidden or unhidden as required - it's just the Null value that's giving me a problem.

The comboboxes are from the Toolbox. Each cb has a ListFillRange on a hidden row on the same sheet. I always include a blank line as part of the ListFillRange as users do not need to complete all boxes. The ListFillRange is a Named range.

Thanks for any help.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
How are you differentiating "" and Null? What constitutes the combo being Null in your mind?
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi xld

Hmmm...

A Null value contains no data, but "" is a zero length string.

It's the 'clear' loop that seems to set the combobox value to Null - so I guess I'm missing something here.
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
If I clear a combox1, the Combobox1.Value = "" returns True.
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
That's what I would have expected as well. But playing with the combobox and stepping through the code, manually changing the box from a value to the blank line, returns a value of Empty. When using the 'clear' routine, the box, when blank, has a value of Null.
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Solved this one - changed from .Value to .Text. All code works just fine now - no errors.
Code:
Private Sub ComboBox8_Change()
Sheets("VRCM").Unprotect

Select Case ComboBox8.Text
    Case Is = "Monthly":
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
     Case Is = "Weekly":
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
    Case Is = Empty:
        If Rows("206:211").Hidden = False Then
            Rows("206:211").Hidden = True
        End If
    Case Else
       If Rows("206:211").Hidden = True Then
           Rows("206:211").Hidden = False
       End If
End Select
    
Sheets("VRCM").Protect
End Sub

Thanks for your input xld.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,476
Messages
5,596,375
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top