Run Macro from Cell Value

[eggtimer]

Hi

I've searched the site and found some cool code, below, which based on a value entered into a cell will run a macro

Well I haven't got that far yet, I thought I would makes sure I could get it to work with the message box first. I pasted the code in entered a value in the cell and the message box appeared, great.

But the cell I want to link it to is not manually updated it is updated via a "spinner" on the forms toolbar and when the spinner updates the value, the message box does not appear.

Which I also find a bit odd as i have to make sure the cell is unlocked on a protected sheet for it to update, which made me think it was bound to work for this.

Does anyone know how to overcome this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("M1")) Is Nothing Then
If Target.Value < 5 Then
MsgBox "replace this line with your macro"
End If
End If
End Sub


Thanks

Wayne

 

[Andrew Poulsom]

Put the code in the Change event procedure for the Spinner.

 

[eggtimer]

Hi Andy

Thanks for the reply

But I'm afraid it has just confused me. I've searched all over for the Change Event Procedure, but couldn't find it.

So i have tried to paste the code into every available place I could find and nothing I have done has made it work.

Where am I going wrong

Cheers

Wayne

 

[Andrew Poulsom]

Use a Spinner from the Control Toolbox instead of from the Forms Toolbar. Its Change event procedure will be in the module for the sheet.

 

[eggtimer]

Hi Andy

Sorry Bud, but, I'm misssing something

I've changed my spinner to activex and thats cool, but i don't know where to past the code

I've tried pasting it below and that changes it from spinbutton1 to worksheet, so i tried pasting it in the middle and i got a compile error

is this the two bits of code i am trying to put together?

Private Sub SpinButton1_Change()

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("M1")) Is Nothing Then
If Target.Value < 5 Then
Rows("8:145").Select
Selection.EntireRow.Hidden = True
End If
End If
End Sub

 

[Andrew Poulsom]

Scrap the Worksheet_Change code and try:

Private Sub SpinButton1_Change() 
   If Spinner1.Value < 5 Then 
      Rows("8:145").EntireRow.Hidden = True 
   Else
      Rows("8:145").EntireRow.Hidden = False
   End If
End Sub

 

[eggtimer]

Andy

You are a Star

Cheers Mate


Wayne