Select Case not seeing Null value

[Glaswegian]

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

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

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.

 

[xld]

How are you differentiating "" and Null? What constitutes the combo being Null in your mind?

 

[Glaswegian]

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]

If I clear a combox1, the Combobox1.Value = "" returns True.

 

[Glaswegian]

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]

Solved this one - changed from .Value to .Text. All code works just fine now - no errors.

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.