another simple quest

[3d]

ok here it is.....I have this bit of code which I found in here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1:G14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub

Now on a clean sheet this works fantastic but the sheet I want to add it to has something already in the Worksheet change part and if I add to that I get a message saying Ambigous Name. I am a little unsure on these automatic loading macros so would appreciate where I can put it to load. This is the begining of the next piece of code:

Option Explicit
Dim iReUsedInk As Integer, iQtyOnStock As Integer
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

it highlites the Dim when it says the Ambigous name

thanx for your time

3D

 

[KniteMare]

You Cannot have two Worksheet change events on the same sheet. Also you cannot Dim the same variable name Outside of the Procedure twice.
Either of these issues may be your problem

You might try to cut the code out and paste it into one of the Worksheet change events and delete the other one.


Yours in EXCELent Frustration

KniteMare

Pringles are like Computers they are at their best when the chips are not broken.

 

[3d]

So does this mean that I can only have one or the other working? not both? grrrrrr any suggestions as now I am lost in waht I can do as I need both the functions

 

[lenze]

On 2002-09-04 09:27, 3d wrote:
So does this mean that I can only have one or the other working? not both? grrrrrr any suggestions as now I am lost in waht I can do as I need both the functions

Not Necessarily. You will just need to figure out how to rewrite the change events in a logical order. You can use IF statements or Select Case, or even a loop. Look at what both change events do and decide the order you wish to check for and then try to write the code.

Example:

If Target is "SOME CRITERIA" do Code1
If Target is "ANOTHER CRITERIA" do Code2

Etc.
This message was edited by lenze on 2002-09-04 09:34

 

[KniteMare]

lenze is exactly correct and perhaps I was not clear. You can have several routines in the Worksheet change event. They may follow each other and be mutually exclusive, or they may be dependant on each other. The case statement is a way to have one or the other action take place, but not both, OR you can put one action below the other and have them run sequentially. Post Both sections here and My self or some one else can show you what I am saying.

Yours in EXCELent Frustration

KniteMare

Not that it matters sir, but doesn't the ground seem to be coming up awfully fast?

 

[3d]

this is one piece of code :
Option Explicit
Dim iReUsedInk As Integer, iQtyOnStock As Integer
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rReUsedInk As Range, rQtyonStock As Range
Dim lTargetRow As Long, lTargetColumn As Long

iReUsedInk = 12
iQtyOnStock = 9

Set rReUsedInk = Cells(Target.row, iReUsedInk)
Set rQtyonStock = Cells(Target.row, iQtyOnStock)


If Target.Column <> 12 Then
Exit Sub
Else
Application.EnableEvents = False
If rReUsedInk.Value <> "" Then
rQtyonStock.Value = rQtyonStock.Value - rReUsedInk.Value
rReUsedInk.ClearContents
End If
If rQtyonStock.Value = "0" Then
lTargetRow = Target.row
lTargetColumn = Target.Column
Range(Cells(Target.row, 1), Cells(Target.row, 12)).Select
Selection.Delete shift:=xlUp
Cells(lTargetRow, lTargetColumn).Select
End If
End If
Application.EnableEvents = True
End Sub


and this is the 2nd piece of code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1:G14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub


thanx for your help guys

 

[Tall]

Hi

Try this one:


Option Explicit
Dim iReUsedInk As Integer, iQtyOnStock As Integer
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rReUsedInk As Range, rQtyonStock As Range
Dim lTargetRow As Long, lTargetColumn As Long

iReUsedInk = 12
iQtyOnStock = 9

Set rReUsedInk = Cells(Target.row, iReUsedInk)
Set rQtyonStock = Cells(Target.row, iQtyOnStock)


If Target.Column <> 12 Then goto NextRoutine
Else
Application.EnableEvents = False
If rReUsedInk.Value <> "" Then
rQtyonStock.Value = rQtyonStock.Value - rReUsedInk.Value
rReUsedInk.ClearContents
End If
If rQtyonStock.Value = "0" Then
lTargetRow = Target.row
lTargetColumn = Target.Column
Range(Cells(Target.row, 1), Cells(Target.row, 12)).Select
Selection.Delete shift:=xlUp
Cells(lTargetRow, lTargetColumn).Select
End If
End If
Application.EnableEvents = True
End Sub


NextRoutine:
If Intersect(Target, Range("A1:G14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub


I hope this will work......

 

[3d]

that came back with errors is there a way I can protect the cells by not putting them with the VB code? I still want to be able to have access to column or 2 of cells. I thaught I could do it with this code but it dont play!

anyone got any ideas?
This message was edited by 3d on 2002-09-05 02:48

 

[KniteMare]

This worked when I ran it here. It will get you started any way.

Enjoy!
"CODE FOLLOWS:

'Option Explicit' Make sure you do not have two of these. _
I REM'd this one out just to show you
'
' this is Your first of piece of code _
with the second piece added and Select Case used to tell _
the routine what to do when. _
The codes are now mutually exclusive.:
Option Explicit
Dim iReUsedInk As Integer, iQtyOnStock As Integer
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rReUsedInk As Range, rQtyonStock As Range
Dim lTargetRow As Long, lTargetColumn As Long
Select Case Target.Column ' I added this
Case 9, 12 'And this

iReUsedInk = 12
iQtyOnStock = 9

Set rReUsedInk = Cells(Target.Row, iReUsedInk)
Set rQtyonStock = Cells(Target.Row, iQtyOnStock)


If Target.Column <> 12 Then
Exit Sub
Else
Application.EnableEvents = False
If rReUsedInk.Value <> "" Then
rQtyonStock.Value = rQtyonStock.Value - rReUsedInk.Value
rReUsedInk.ClearContents
End If
If rQtyonStock.Value = "0" Then
lTargetRow = Target.Row
lTargetColumn = Target.Column
Range(Cells(Target.Row, 1), Cells(Target.Row, 12)).Select
Selection.Delete shift:=xlUp
Cells(lTargetRow, lTargetColumn).Select
End If
End If
Application.EnableEvents = True
Case Else 'and this
'Your EXTRA code is pasted here
If Intersect(Target, Range("A1:G14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Select 'and this

End Sub


'and this 2nd piece of code is REM'd out just to show _
you what I did:

'Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'If Intersect(Target, Range("A1:G14")) Is Nothing Then Exit Sub
'Application.EnableEvents = False
'MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
'Application.Undo
'Application.EnableEvents = True
'End Sub
'
'CODE ENDS

Yours in EXCELent Frustration

KniteMare

Mommy, can I go out and play now?

 

[3d]

thanx that worked an absolute dream it is much appreciated and thankyou 4 the lesson in the REM

thankyou