isempty() event macro help

[largeselection]

As a follow-up to a previous post...

I'm trying to get a macro to run on a change in a cell. Here's what I have so far:


Private Sub TestClick()
If Not IsEmpty(Target) Then
Sheets("Sheet 3").Columns("A:A").Hidden = True
Else
End If
End Sub

So if the target cell is not empty then hide column A on "Sheet 3".

(The target cell toggles empty/not empty on click as part of a different event macro).

But...It's not working! Any ideas what could be going awry?

 

[seenfresh]

Check this site out:

http://www.ozgrid.com/VBA/run-macros-change.htm

If you still have trouble let me know.

 

[largeselection]

Thanks for your reply! I read that link a different time when I was going through a different part of code.

I do have an event macro that works well to put a check mark in a cell based on clicking on a target cell (thanks to some mrexcel MVPs).

but I still don't see why the second part isn't working...
When I click on a cell, a checkmark appears so the target is not empty anymore so it should go to "sheet 3" and hide column A.


Private Sub TestClick()
If Not IsEmpty(Target) Then
Sheets("Sheet 3").Columns("A:A").Hidden = True
Else
End If
End Sub

 

[Smitty]

Can you post your change code?

You're probably better off incorporating the hide part there, then trying to do it here.

As a follow-up to a previous post...

If this is related to a previous post, you should stick to that one.

 

[largeselection]

Hi! It is related to a different post because everytime one problem is fixed another one is created hahah or you alter the way I view the original problem....

This is the original change code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Target.Formula = "=CHAR(252)"
Target.Value = Target.Value
With Target.Font
.Name = "Wingdings"
.FontStyle = "Bold"
.Size = 8
End With
Target.Borders.LineStyle = xlContinuous

Else

Target.ClearContents

End If
End Sub

So this puts a checkmark in the cell when it is clicked. I can tag on the second part of code within the above, but then applies to all cells in the first column that are clicked (and ultimately I'm going to tailor it so that when I check a box a certain column will hide). So I could just copy this code plus the second part a lot of times for the many targets (400 or so), but there must be an easier way.

 

[Richard Schollar]

Hi

You don't pass Target as an argument to the sub and you obviuously don't have Option Explicit declared at the top of your subs so when Excel starts executing your TestClick sub it sees 'Target' as an undeclared variable, instantiates it which means that it is of Variant data type and will satisfy the Isempty() test. Hence, the If test always returns False.

So, you could re-write what you have like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Target.Formula = "=CHAR(252)"
Target.Value = Target.Value
With Target.Font
.Name = "Wingdings"
.FontStyle = "Bold"
.Size = 8
End With
Target.Borders.LineStyle = xlContinuous

Else
Call TestClick(Target)
Target.ClearContents

End If
End Sub
 
Private Sub TestClick(ByVal Target As Range)
If Not IsEmpty(Target) Then
Sheets("Sheet 3").Columns("A:A").Hidden = True
Else
End If
End Sub