Hide shape based on combobox selection.

[gino59]

Hi all!

I am trying to hide an autoshape based on a cell change. The cell change is caused by a combobox selection. If I manually type into the combobox's linked cell, the shape goes away. If I just have the combobox make the change to the cell value, the combobox stays visible.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Sheets("FrontPage").Range("L55").Value > _
          Sheets("FrontPage").Range("L56").Value Then
               ActiveSheet.Shapes("shpSTOP").Visible = True
     Else
          ActiveSheet.Shapes(shSTOP).Visible = False
     End If
End Sub

What am I missing????

Many thanks as always!
Gino

 

[Jerry Sullivan]

Hi Gino,

I could be wrong on this, but in doing a little testing it appears each time the linked cell is updated by the combobox, it is considered Text instead of a Number - regardless of the formatting of the linked cell prior to the change.

Instead of having a Worksheet_Change event that reads a linked cell updated by combobox, you might try just having the Combobox1_Change event trigger a function that does the comparison and update.

Something like...

'copy this into the Worksheet's Code
Private Sub ComboBox1_Change()
    Update_Shape (ComboBox1.value)
End Sub
 
'copy this into a Standard Module
Public Function Update_Shape(lngValue As Long)
    If lngValue > Sheets("FrontPage").Range("L56").value Then
        ActiveSheet.Shapes("shpSTOP").Visible = True
    Else
        ActiveSheet.Shapes("shpSTOP").Visible = False
    End If
End Function

If you're using a combobox that allows users to enter non-numeric values, you'll want to add a test that the value passed to the function is numeric.

 

[gino59]

Hi Jerry,

Thanks! Unfortunately, I get a run-time error '424' object required error.

???

Cheers,

Gino

 

[Jerry Sullivan]

Gino,
You'll need to check to make sure that object names in my example match yours.
Mine assumes your Shape's name is "shpStop". In your OP you listed "shpStop" in one place and shpStop (no quotes which is a variable) in another.

It also assumes you're combobox is named ComboBox1.
You'll also want to make sure you delete your previous Worksheet_Change code.
If that doesn't fix it, what line of code did the debugger stop at?

 

[gino59]

That's it, Jerry! The shape name was wrong ("shpStop" vs shpStop)! Which of course was causing the object not defined error!. Now it works as it should. As the cbox list is derived from a named list, any thing not on the list does cause an error.

Many thanks!!!

Gino

 

[Jerry Sullivan]

Glad that worked Gino.

You could you use a Listbox instead of a Combobox if you don't want to allow the users to enter values that are not on your list.

If you stick with the combobox, just let me know if you want some help writing a check for invalid entries typed into your combobox.

I assumed the value had to be a whole number. If fractions are okay too, you'll want to change the function parameter to be a Double data type instead of a Long.

 

[gino59]

Thanks again, Jerry. The Listbox won't work as it would take up too much space (way too many entries). The numbers are always whole numbers as well.

Cheers!
Gino