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