Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > worksheet_change help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default worksheet_change help
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default worksheet_change help
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


On Jun 25, 9:01*am, Mike B. wrote:
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default worksheet_change help
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

"Mike B." wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 245
Default worksheet_change help
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)

"Mike B." wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 523
Default worksheet_change help
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." wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default worksheet_change help
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default worksheet_change help
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


On Jun 25, 10:41*am, Mike B. wrote:
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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default worksheet_change help
actually i just noticed you have SELECTIONchange and not just CHANGE
but in either event it should do something.
susan


On Jun 25, 10:41*am, Mike B. wrote:
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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default worksheet_change help
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" wrote:

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


On Jun 25, 10:41 am, Mike B. wrote:
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


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,117
Default worksheet_change help
oh good! i'm glad it worked!
susan

On Jun 25, 11:22*am, Mike B. wrote:
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" wrote:
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

On Jun 25, 10:41 am, Mike B. wrote:
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- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet_Change ram Excel Programming 8 January 5th 06 11:13 PM
Worksheet_change Andrew haycock Excel Programming 2 September 3rd 03 05:32 PM
worksheet_change vs. calculate, and worksheet_change not running Tom Ogilvy Excel Programming 1 July 14th 03 02:51 AM
worksheet_change vs. calculate, and worksheet_change not running Ross[_5_] Excel Programming 0 July 13th 03 04:27 PM


All times are GMT +1. The time now is 10:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement