Boolean Variable

[Evagrius Ponticus]

Hi All,

In a worsheet, any time a user activates a cell in column G in rows 1 through 16 a picture will appear - each row has a different picture. Once a user activates any row outside that range, any visible picture will be hidden. I don't want this code to run at every selection change because it is causing the screen to flicker even with screen updating set to false.

So, once a user selects a cell outside rows 1 through 16, any visible picture will be hidden. Then if a user selects still another cell outside the range - the code should not run again since no pic is visible.

I tried using a static variable, but it always starts at FALSE so that didn't work. Can anyone please advice?

Static MyVar As Boolean
If MyVar = false then exit sub
Rem SHOW PICTURES
If ActiveCell.Address = Range("New").Address Then
ActiveSheet.Shapes("House").Visible = True: MyVar = True
Else
ActiveSheet.Shapes("House").Visible = False: MyVar = False
End If
    If MyVar = True Then GoTo End
    If ActiveCell.Address = Range("ronW").Address Then
    ActiveSheet.Shapes("White").Visible = True: MyVar = True
    Else
    ActiveSheet.Shapes("White").Visible = False: MyVar = False
    End If

 

[MickG]

Can you modify this to get what you want !!
This is just for 4 Pictures "G1:G4".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("G1:G4")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target.Address(0, 0)
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "G1": ActiveSheet.Shapes("Picture 1").Visible = True
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "G2": ActiveSheet.Shapes("Picture 2").Visible = True
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "G3": ActiveSheet.Shapes("Picture 3").Visible = True
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "G4": ActiveSheet.Shapes("Picture 4").Visible = True
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Else[/COLOR]
    ActiveSheet.Pictures.Visible = False
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Evagrius Ponticus]

Hi MickG! Thank you for the help. I am having the same issue with your suggestion though . . the code is called everytime a cell is selected - regardless of the range.

If a cell outside the defined range is selected -this portion still runs.

[COLOR=#000080]Else[/COLOR]
    ActiveSheet.Pictures.Visible = False
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[code]

 

[shg]

A little tweak to Mick's code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rInt        As Range
    Dim cell        As Range
    Static bInit    As Boolean
    Static bVis     As Boolean
 
    Set rInt = Intersect(Target, Range("G1:G4"))
    
    If rInt Is Nothing Then
        If bVis Or Not bInit Then
            Me.Pictures.Visible = False
            bVis = False
            bInit = True
        End If
    Else
        bVis = True
        For Each cell In rInt
            Select Case cell.Address(0, 0)
                Case Is = "G1": Me.Shapes("Picture 1").Visible = True
                Case Is = "G2": Me.Shapes("Picture 2").Visible = True
                Case Is = "G3": Me.Shapes("Picture 3").Visible = True
                Case Is = "G4": Me.Shapes("Picture 4").Visible = True
            End Select
        Next cell
    End If
End Sub

 

[Evagrius Ponticus]

Ahhhh - the infrequent high of success!!!! Thank you Shg. I need to spend a few minutes figuring out what you did - but I think I can get after a few step throughs - thank you sir

Thanks for improving the code MickG!

If I may ask one more question . .is it necessary to place the (0,0) after the target.address?

 

[shg]

is it necessary to place the (0,0) after the target.address?

It just causes it to returns a relative reference (G1 instead of $G$1).

I find typing dollar signs tiresome and do the same thing -- and the string comparison is a little faster with fewer characters.

 

[Evagrius Ponticus]

I understand - thank you again good sir!!

 

[Scott Huish]

The Is = could also be dropped as that is the default.

 Case "G1": Me.Shapes("Picture 1").Visible = True
 Case "G2": Me.Shapes("Picture 2").Visible = True
 Case "G3": Me.Shapes("Picture 3").Visible = True
 Case "G4": Me.Shapes("Picture 4").Visible = True