Solved: Code is calling Private Worksheet Code, Why?

[YellowLabPro]

During this loop;

If Wss.Cells(i, 7) = "x" Then
LRowt = Wst.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).End(xlUp).Row + 1
Wst.Cells(LRowt, 2).Value = Wss.Cells(i, 4).Value 'Item#
Wst.Cells(LRowt, 3).Value = Wss.Cells(i, 1).Value 'Item
Wst.Cells(LRowt, 4).Value = Wss.Cells(i, 2).Value 'Color
Wst.Cells(LRowt, 7).Value = Wss.Cells(i, 26).Value 'Cost
Wst.Cells(LRowt, 9).Value = Wss.Cells(i, 6).Value 'Delivery
End If
Next i


This loop calls some Private worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 12 Then Exit Sub

It exits the code, but why is it calling it up in the first place?

[lucas]

Hi Yelp,
It looks to me like(I'm guessing) that your sheet change code is in sheet Wst and when a value is changed there as I think your first code is doing:
Wst.Cells(LRowt, 2).Value = Wss.Cells(i, 4).Value 'Item#

Looks like Wst sheet is changing to a value derived from Wss sheet and that triggers the sheet change event......

[YellowLabPro]

Hellooo Steve,

Aahhhhhhhhhhhhh--

Would this be what you are referring to as sheet change code? I have code there, but not familiar w/ the term or the inter-connectivity that you seem to be referring to.

The sheet code is this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 12 Then Exit Sub
Target.FormatConditions.Delete
Select Case UCase(Target.Value)
Case "G":
Target.Interior.ColorIndex = 6
Case "X":
Target.Interior.ColorIndex = 44
Case "B":
Target.Interior.ColorIndex = 33
Case "G1":
Target.Interior.ColorIndex = 38
Case "G2":
Target.Interior.ColorIndex = 39
Case "S":
Target.Interior.ColorIndex = 45
Case "Y":
Target.Interior.ColorIndex = 40
Case "H":
Target.Interior.ColorIndex = 43
Case "SB1":
Target.Interior.ColorIndex = 4
Case "SB2":
Target.Interior.ColorIndex = 42
Case "-":
Target.Interior.ColorIndex = 36
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End Sub

[lucas]

Yes...if you type for instance a G in column 12 it changes the color of the cell....if you run a macro that puts data in any other column besides 12 or selects more than one cell on that sheet then the code does what you told it to do...it exits the sub.....that's my thought...maybe someone else will have a different idea..

[malik641]

YLP,

From my understanding, the _Change event isn't exactly "Called" by your procedure, but rather "triggered". When you change a value in a worksheet (say Sheet1) then that worksheet's class module i.e. "Sheet1" event _Change is triggered by the change of the value. Also, in the ThisWorkbook class module, the event _SheetChange() event is also triggered.

BTW, I started typing this before Steve came in...so in response to what has been going on since then...

Steve is right. :) (sorry if you're expecting something more lol)

[lucas]

I saw you lurking Joseph...was wondering if you were going to back me up or straighten me up.....:devil2:

How's sunny Fla......just got back a few weeks ago from California...it's just not the same.

[YellowLabPro]

Thanks Malik,
That does help shed light on what is going on in the background....

:beerchug:

Cheers to both of you

[malik641]

YLP,
Glad to help you understand :) Did you get a VBA book yet? If so, which one? There should be a section that helps explains Excel Events.


Steve,

I figured I'd be on your side this time :devil2:

Florida's awesome :cool: California eh? When you say "not the same", do you mean you miss Cali, or it's not the same as the Sunshine State :)

And yes, I've been lurking a bit and trying to post a bit. I've also been reading old KB approved items to see how you guys handle things so I can get started.

I've had a tough time getting on VBAX at my job, they have me doing a lot as the company grows. But I've been learning new technologies at the same time. Like recently I updated a SQL Server 2000 database from CorelDraw using ADO. Helped out a lot with our drawing production :) I also started sort of an Electronic Signature thing to approve drawings that we make and are eFiled instantly from CorelDRAW, pdf'ed and all :)
I've also been real busy with my new home. Painting, re-caulking things, house-shopping...sheesh!!! too much, man!! But we've just finished the office and I'm not taking any summer classes soooo I should be back in top gear at VBAX soon :thumb

[johnske]

Following on - if you're making changes with VBA in a standard code module to a worksheet that has worksheet change event code in it, the changes cause the event code to be triggered, if you don't want the event code triggered by these changes you need to put Application.EnableEvents = False in the code in the standard module before you make any changes and Application.EnableEvents = True in the code after the changes have been made.

[malik641]

Following on - if you're making changes with VBA in a standard code module to a worksheet that has worksheet change event code in it, the changes cause the event code to be triggered, if you don't want the event code triggered by these changes you need to put Application.EnableEvents = False in the code in the standard module before you make any changes and Application.EnableEvents = True in the code after the changes have been made.
Thanks John. I forgot to mention that :doh:

[johnske]

Dunno why the board's enclosing every thing in quotes, I'll get Jake to look into it...

[malik641]

I did that on purpose :) Cause I saw you quoted yourself so I thought I would over-do-it :rofl:

[YellowLabPro]

Thanks Malik and John.

Malik I did get a couple of books, but don't find them all that helpful.
I got J.Walkenbach VBA 2003 book and was looking through there tonight for some For Each Next Loop info and could not find anything on it.

I would actually like to find a book that really tears into the heart of things w/ VBA.... The VBA for dummies is good for touching on things... but not too in-depth.

[malik641]

I'm surprised you didn't find what you're looking for with Walkenbach's book...that's where I started (in conjunction with this site). And maybe it just depends on the person, but I feel that it's kinda better to learn how to do what before you start getting into "why" it actually works...but that's just me.

If you're looking for an in-depth book, then I would suggest Programming Microsoft Visual Basic 6.0 (Paperback) By Francesco Balena (http://www.amazon.com/Programming-Microsoft-Visual-Basic-6-0/dp/0735605580/ref=sr_1_13/105-2782448-3919643?ie=UTF8&s=books&qid=1180243945&sr=1-13). This book is truly Invaluable to me. It really gets into some good topics and has great tips for programming VB.

[YellowLabPro]

Joseph, if you still have that book handy, how about look in it and see if you can find info on For Each Next Loops. There is a little on For Next....

I find a lot of the topics in there are indexed oddly, hard to find at times. But maybe that is me.

[YellowLabPro]

I have read good reviews on this book
http://www.amazon.com/Excel-2002-VBA-Programmers-Reference/dp/0764543717/ref=sr_1_47/102-3322120-5037751?ie=UTF8&s=books&qid=1180244919&sr=1-47
but have not looked it over myself.
The book you mention is for VB, how does that translate back into VBA?

[malik641]

The book you mention is for VB, how does that translate back into VBA?It is virtually the same. VBA basically means that the code is "hosted" in a seperate application (like MS Excel) where it is compiled and run there...whereas in VB you would normally create Executable files. I'm sure my explanation is a bit off (or maybe more), but it's the general idea to me.

I haven't read/skimmed that book you mentioned, but let me give you a quick glimpse at the book I'm talking about (also available in eBook format).


Iterating on Collection objects

Since you can address items using their numeric indices, you can loop on all the elements of a Collection object using a regular For?Next loop:
' Load the contents of a Collection into a ListBox control.
Dim i As Long
For i = 1 To EmployeeNames.Count
List1.AddItem EmployeeNames(i)
Next

While this code works, Collection objects offer another, better way to perform the same task, based on the For Each?Next loop:
Dim var As Variant
For Each var in EmployeeNames
List1.AddItem var
Next

Notice that the loop's controlling variable (var, in this example) must be of type Variant so that it can host any value that had been added to the Collection. The only exception to this rule is when you're sure that the Collection contains only a given class of objects (forms, controls, or user-defined objects), in which case you can use a controlling variable of that specific type:
' If the Customers collection includes only references
' to individual Customer objects
Dim cust As Customer
For Each cust In Customers
List1.AddItem cust.Name
Next

Using a controlling variable of a specific object type usually offers better performance than a generic Variant or Object variable. Iterating on the elements of a collection using a For Each?Next loop is generally faster than a regular For?Next loop because the latter forces you to refer to individual elements using their numeric indices, which is a relatively slow operation.


Loop Statements

The most frequently used looping structure in Visual Basic is undoubtedly the For...Next loop:
For counter = startvalue To endvalue [Step increment]
' Statements to be executed in the loop...
Next

You need to specify the Step clause only if increment is not equal to 1. You can exit the loop using an Exit For statement, but unfortunately Visual Basic doesn't provide any sort of "Repeat" command that lets you skip the remaining part of the current iteration and restart the loop. The best you can do is use (nested) If statements or, if you don't want to make the logic too complex, use a plain GoTo keyword that points to the end of the loop. In fact, this is one of the few occasions when a single GoTo statement can make your code more readable and maintainable:
For counter = 1 To 100
' Do your stuff here ...
' if you want to skip over what follows, just GoTo NextLoop.
If Err Then Goto NextLoop
' more code that you don't want to enclose within nested IF blocks
' ...
NextLoop:
Next


TIP Always use an Integer or Long variable as the controlling variable of a For...Next loop because they're faster than a Single or a Double controlling variable, by a factor of 10 or more. If you need to increment a floating-point quantity, the most efficient technique is explained in the next example.
CAUTION A compelling reason to stay clear of floating-point variables as controlling variables in For...Next loops is that, because of rounding errors, you can't be completely sure that a floating-point variable is incremented correctly when the increment is a fractional quantity, and you might end up with fewer or more iterations than expected: Dim d As Single, count As Long
For d = 0 To 1 Step 0.1
count = count + 1
Next
Print count ' Displays "10" but should be "11"

When you want to be absolutely sure that a loop is executed a given number of times, use an integer controlling variable and explicitly increment the floating-point variable within the loop:
Dim d As Single, count As Long
' Scale start and end values by a factor of 10
' so that you can use integers to control the loop.
For count = 0 To 10
' Do what you want with the D variable, and then increment it
' to be ready for the next iteration of the loop.
d = d + 0.1
Next

I covered the For Each...Next loop already in Chapter 4 (http://vbaexpress.com/forum/ch04a.htm), and I won't repeat its description here. I just want to show you a neat trick that's based on this type of loop and the Array function. This technique permits you to execute a block of statements with different values for a controlling variable, which don't need to be in sequence:
' Test if Number can be divided by any of the first 10 prime numbers.
Dim var As Variant, NotPrime As Boolean
For Each var In Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29)
If (Number Mod var) = 0 Then NotPrime = True: Exit For
Next

The values don't even have to be numeric:
' Test if SourceString contains the strings "one", "two", "three", etc.
Dim var2 As Variant, MatchFound As Boolean
For Each var2 In Array("one", "two", "three", "four", "five")
If InStr(1, SourceString, var2, vbTextCompare) Then
MatchFound = True: Exit For
End If
Next

The Do...Loop structure is more flexible than the For...Next loop in that you can place the termination test either at the beginning or the end of the loop. (In the latter case, the loop is always executed at least once.) You can use either the While clause (repeat while the test condition is True) or the Until clause (repeat while the test condition is False). You can exit a Do loop at any moment by executing an Exit Do statement, but?as with For...Next loops?VBA doesn't offer a keyword that skips over the remaining statements in the loop and immediately restarts the loop.
' Example of a Do loop with test condition at the top.
' This loop is never executed if x <= 0.
Do While x > 0
y = y + 1
x = x \ 2
Loop

' Example of a Do loop with test condition at the bottom.
' This loop is always executed at least once, even if x <= 0.
Do
y = y + 1
x = x \ 2
Loop Until x <= 0

' Endless loop: requires an Exit Do statement to get out.
Do
...
Loop

The While...Wend loop is conceptually similar to the Do While...Loop. But you can test the condition only at the beginning of the loop, you don't have an Until clause, and you don't even have an Exit While command. For these reasons, most programmers prefer the more flexible Do...Loop structure, and in fact you won't see a single While...Wend loop in this entire book.

[YellowLabPro]

This looks interesting....
I will stop by a book store to read more....

[malik641]

It is well worth it.

[Bob Phillips]

I have read good reviews on this book
http://www.amazon.com/Excel-2002-VBA-Programmers-Reference/dp/0764543717/ref=sr_1_47/102-3322120-5037751?ie=UTF8&s=books&qid=1180244919&sr=1-47
but have not looked it over myself.
The book you mention is for VB, how does that translate back into VBA?
That book is very good, one of the two best books on the market I would venture (the other being Professional Excel Development by the same authors).

The problem you are having YLP IMO is trying to run before you walk. You have to build up the skills bit by bit, You can't delve into some of the more complex items until you have mastered their foundations. In this respect, no one book can ever serve your entire needs. I have just looked on my book shelves, and I see that I have 12 books on Excel/VBA, 214 on VB, 2 on ADO, 2 on SQL Server, plus at leats 20 more on theory and general topics (databases, graphic design etc.) I am not suggesting that you buy this many (many of mine are review copies, I have only actually shelled out hard cash on 2 Excel books for instance), but it does show the breadth and so on that you can (need to?) cover.

As to Francesco's book that Joseph recommends, it is a good book and Francesco writes well (I even thought about buying it myself once), but it is a VB book, not VBA, not Excel. The problem here is that it will deal with the syntax fine, but one of the most important things in VBA is understanding the object model of the host.

In short, no quick route buddy, just hard work, practice, trial and error, and experience.

[cavemonkey]

Hi guys I'm back with questions and problems also.

I'm now supposed to put another section in the 'summary' page which states if the alarm is currently activated or not. I managed to get my code work once or twice but oddly enough after I lump all the subs together into a button, it does not seem to work anymore.

Currently I do not have the code with me right now. Will post it up asap.

Thanks

[mdmackillop]

I will stop by a book store to read more....
A little bit of Scots blood in there it seems!

[YellowLabPro]

Cavemonkey,
I think you may have misposted in the wrong post

[malik641]

...I have 12 books on Excel/VBA, 214 on VB, 2 on ADO, 2 on SQL Server, plus at leats 20 more on theory and general topics (databases, graphic design etc.)214 on VB :eek: whew!! Way to go, Bob :clap: That's impressive!

[YellowLabPro]

I thought the same thing, but Bob as also admitted that his fingers do a lot of dancing to wild dance numbers on the keyboard. I figured he was doing a break-dance number and stepped on an additional digit there....:rofl: