Need help with macro...

J

Jenn

I need to have a macro run based on a cell value. I have a "data
validation" drop down box. (Not a form dropdown box.)

If drop down box is equal to NEW STORE PROJECT then run macro.

Here is the macro...

Sub Unhide_Column()
'
' Unhide_Column Macro
'

'
Columns("A:C").Select
Selection.EntireColumn.Hidden = False
Range("B13").Select
End Sub

....Would I put an "if" formula there...or ...Hummm. I'm at a lost with
this one. Any guru's able to help?

Thanks in advance! Have a great day!
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Don Guillett

You would want to use a worksheet_change event

if target.address<> "$F$1" then exit sub 'your cell
if target="NEW STORE PROJECT" then Columns("a:c").Hidden = False
 
D

Dave Peterson

Excel has some worksheet events that you can tie into. If something changes on
a worksheet, you can check to see what cell it was and what value it became.

If you want to try this, rightclick on the worksheet tab that should have this
behavior and select view code. Then paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

If LCase(Target.Value) = LCase("NEW STORE PROJECT") Then
Me.Columns("A:C").EntireColumn.Hidden = False
End If

End Sub

A couple of things...

#1. Change A1 to the cell that you want to inspect.
#2. Instead of calling the separate macro, I just did the work in this
procedure. And I dropped the .select's.
#3. xl97 has a bug that can make this kind of thing fail with data|validation
cells. Debra Dalgleish explains it here:
http://contextures.com/xlDataVal08.html#Change
 
J

Jenn

WOW...Thank you everyone for your response. Let me fool around with
these codes and I'll let you know how I make out.

Have a great day!

Jenni
 
J

Jenn

This is PPPPPPPEEERRRFECT!

Now..my next question is...what if they "change their mind" and select
another option from the dropdown. The code doesn't "hide" it.

You men are fabulous! Helping me do my job. :) Big smiles and happy
Friday!
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Columns("A:C").Hidden = True
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jenn

Yay...it worked. Thank you thank you thank you!

Have a great weekend!

Jenni



Bob said:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Columns("A:C").Hidden = True
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
J

Jenn

Yay...it worked. Thank you thank you thank you!

Have a great weekend!

Jenni



Bob said:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Columns("A:C").Hidden = True
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
J

Jenn

I'm so sorry...one more question.

This is a protected sheet with no password. Any way to enable the
function written in the code?


Thanks!
 
D

Don Guillett

I just recorded this simple macro.
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 11/17/2006 by Don Guillett
'

'
ActiveSheet.Unprotect
ActiveCell.FormulaR1C1 = "dd"
Range("E16").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

modify to suit and put your change event code in it

ActiveSheet.Unprotect

whichever change event presented you like the best


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
Me.Columns("A:C").Hidden = True
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jenn

Thank you very much to everyone. It is perfect!

Enjoy your weekend.

Jenni



Bob said:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== DV cell, change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
Me.Columns("A:C").Hidden = True
If .Value = "NEW STORE PROJECT" Then
Me.Columns("A:C").Hidden = False
Me.Range("B13").Select
End If
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Top