problem with code

[chobanne]

hello all

I make this code and have problem with it

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("ah1") = 1 Then
ActiveSheet.Shapes("Picture 15").Visible = True
Else:
ActiveSheet.Shapes("Picture 15").Visible = False
End If
End Sub

The picture don't automatically hide or unhide until i click some random cell in sheet.

Can someone pls help me what to add in code to avoid subsequent click

 

[mohadin]

Hi
Try

Private Sub Worksheet_Change(....)

 

[chobanne]

not working

 

[Alex Blakenburg]

Is there a cell on the sheet that you are changing and is meant to trigger the code ?
(the change needs to be a manual change and not a formula, if a formula is there a cell that is manually changed that drives the formula)

Maybe:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Target, Range("AH1")) Is Nothing Then
        If Range("AH1") = 1 Then
            ActiveSheet.Shapes("Picture 15").Visible = True
        Else:
            ActiveSheet.Shapes("Picture 15").Visible = False
        End If
    End If
End Sub

 

[chobanne]

Is there a cell on the sheet that you are changing and is meant to trigger the code ?
(the change needs to be a manual change and not a formula, if a formula is there a cell that is manually changed that drives the formula)

Yes there is cell AH1 with formula =IF(AG=TRUE,1,0)

 

[chobanne]

Is there a cell on the sheet that you are changing and is meant to trigger the code ?
(the change needs to be a manual change and not a formula, if a formula is there a cell that is manually changed that drives the formula)

Maybe:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Target, Range("AH1")) Is Nothing Then
        If Range("AH1") = 1 Then
            ActiveSheet.Shapes("Picture 15").Visible = True
        Else:
            ActiveSheet.Shapes("Picture 15").Visible = False
        End If
    End If
End Sub

the code is not working

 

[Alex Blakenburg]

With your original code, it will run the code everytime you move around the sheet (in fact any random cell).
Of course if AH1 doesn't change it should do the same thing each time so there should be no visible change on the spreadsheet.

Worksheet_Change only runs when you actually change something on the worksheet.
What makes AG change ? Is it also a formula ? Actually it can't be AG that is not valid, what is the actual cell reference in AG

 

[chobanne]

With your original code, it will run the code everytime you move around the sheet (in fact any random cell).
Of course if AH1 doesn't change it should do the same thing each time so there should be no visible change on the spreadsheet.

Worksheet_Change only runs when you actually change something on the worksheet.
What makes AG change ? Is it also a formula ? Actually it can't be AG that is not valid, what is the actual cell reference in AG

checkbox click

 

[Alex Blakenburg]

Do you know if its an ActiveX check box or a Forms check box ?
If its ActiveX in the same module as the worksheet event copy in this.

The checkbox name needs to match.

Private Sub CheckBox1_Click()
        If Range("AH1") = 1 Then
            ActiveSheet.Shapes("Picture 15").Visible = True
        Else:
            ActiveSheet.Shapes("Picture 15").Visible = False
        End If
End Sub

 

[chobanne]

Do you know if its an ActiveX check box or a Forms check box ?
If its ActiveX in the same module as the worksheet event copy in this.

The checkbox name needs to match.

Private Sub CheckBox1_Click()
        If Range("AH1") = 1 Then
            ActiveSheet.Shapes("Picture 15").Visible = True
        Else:
            ActiveSheet.Shapes("Picture 15").Visible = False
        End If
End Sub

its form check box, thats why i have AH1 value, For activex i dont need range AH1 i think, i think code must be modify