worksheet_change help

[Mike B.]

I have a macro that I want to apply to a specific worksheet. So that when I
go to the worksheet and change any cell, the sheet will automatically call
the macro.

I have R4R5Reset already in a module and know I have to go to the specific
sheet to do some private sub worksheet_????. I just don't know how to get to
call it everytime I change any cell on the sheet.

Option Explicit

Sub R4R5Reset()

'Define Variables
Dim InPutOne, InPutTwo, InPutThree As Double
Dim OutPutOne, OutPutTwo As Range
Dim Input3 As Range
Dim first As Boolean
Dim last As Boolean

'Set Variables
InPutOne = Range("AA12").Value
InPutTwo = Range("C11").Value
Set OutPutOne = Range("Y6")
Set OutPutTwo = Range("X23")
Set Input3 = Range("S16")

'Booleans
first = OutPutOne.Value = "UNLATCH"
last = OutPutTwo.Value = "LATCH"

'Different scenarios
If InPutOne = 0 And InPutTwo = 0 And first Then
InPutThree = 0
ElseIf InPutOne = 0 And InPutTwo = 1 Then
InPutThree = 1
ElseIf InPutOne = 1 And InPutTwo = 1 Then
InPutThree = 1
ElseIf InPutOne = 0 And InPutTwo = 0 And first = False Then
InPutThree = 1
first = True
ElseIf InPutOne = 1 And InPutTwo = 0 Then
InPutThree = 0
End If

'Results
If InPutThree = 0 Then
OutPutOne.Value = "UNLATCH"
OutPutTwo.Value = ""
Input3.Value = 0
ElseIf InPutThree = 1 Then
OutPutTwo.Value = "LATCH"
OutPutOne.Value = ""
Input3.Value = 1
End If
End Sub

 

[Susan]

in the worksheet, right click on the tab you want this associated with
and choose "view code".
then cut & paste your macro there, using this language at the top
instead of Sub R4R5Reset().

Private Sub Worksheet_Change(ByVal Target As Range)

i believe that if you don't specify a target, it should work anytime
anything is changed anywhere on the sheet. i could be wrong about
that, so there may be an additional fixing you'll need.

hope it gets you started
susan

 

[Mike H]

Maybe this,

Depending on what you are doing it may be necessary to use
application.screenupdating =false/true to stop an endless loop

Private Sub Worksheet_Change(ByVal Target As Range)
R4R5Reset
End Sub

Sub R4R5Reset()
'do something
End Sub


Mike

 

[Office_Novice]

When i wrote it i had it in a button click event, but any action event ca
trigger the macro. if i read what you want right try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Sam Wilson]

In the Project Explorer window, right click the worksheet you want to use as
the trigger for calling the macro, and click view code. There are two white
boxes at the top of the code window, one says (General) and the other
(Declarations)

Change General to "Worksheet", change Declarations to "Change", and in the
sub it generates for you, use the following code:

Application.enableevents = false
Call R4R5Rest
Application.enableevents=true

Sam

 

[Mike B.]

I right clicked the the specific sheet in the Project Explorer window. I
clicked view code and pasted the following code into the specific sheet. When
I change InPutOne or InPutTwo, no action takes place and I don't get the
sheet to change.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Declare your Variables
Dim InPutOne, InPutTwo, InPutThree As Double
Dim OutPutOne, OutPutTwo As Range
Dim Input3 As Range
Dim first As Boolean
Dim last As Boolean

'Tell Excel what your Variables are
InPutOne = Range("AA12").Value
InPutTwo = Range("C11").Value
Set OutPutOne = Range("Y6")
Set OutPutTwo = Range("X23")
Set Input3 = Range("S16")

'Booleans
first = OutPutOne.Value = "UNLATCH"
last = OutPutTwo.Value = "LATCH"

'Tell Excel What to do in each "if"
If InPutOne = 0 And InPutTwo = 0 And first Then
InPutThree = 0
ElseIf InPutOne = 0 And InPutTwo = 1 Then
InPutThree = 1
ElseIf InPutOne = 1 And InPutTwo = 1 Then
InPutThree = 1
ElseIf InPutOne = 0 And InPutTwo = 0 And last Then
InPutThree = 1
ElseIf InPutOne = 1 And InPutTwo = 0 Then
InPutThree = 0
End If

If InPutThree = 0 Then
OutPutOne.Value = "UNLATCH"
OutPutTwo.Value = ""
Input3.Value = 0
ElseIf InPutThree = 1 Then
OutPutTwo.Value = "LATCH"
OutPutOne.Value = ""
Input3.Value = 1
End If

End Sub

 

[Susan]

i dunno........
i put this in an empty spreadsheet & entered a value in a random cell
& it worked.
=================
Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Hi there!"

End Sub
=================
you actually made a change (as opposed to just clicking on the cell)?
if so, i don't know why it wasn't triggered. try running this little
sub separately......

sub reset()
application.screenupdating = true
application.enableevents = true
end sub

sometimes when you're playing with code you get out of a code while
enableevents is set to false & it doesn't get reset. after you run
this little code (nothing visible will happen), try your change again.
susan

 

[Susan]

actually i just noticed you have SELECTIONchange and not just CHANGE
but in either event it should do something.
susan

 

[Mike B.]

Thank you very much. I tried your msgbox on an empty spreadsheet, but it
still wouldn't appear. I then saw your reset macro and I was able to get the
msgbox. So simply put: "application.enableevents = true" right under Private
Sub and it works very well.

 

[Susan]

oh good! i'm glad it worked!
susan

Thank you very much. I tried your msgbox on an empty spreadsheet, but it
still wouldn't appear. I then saw your reset macro and I was able to get the
msgbox. So simply put: "application.enableevents = true" right under Private
Sub and it works very well.










- Show quoted text -