NemoTheBlue


So someone in the office said they wanted to keep a workbook more organised, instead of having to manually make changes to several worksheets to reflect the same information. Naturally, being the helpful soul I am, I volunteered to have a go at it. Then I realised just how basic my knowledge of VBA was! I've done a lot of Googling and trolled through forums and come up with the following so far, but it's still not working for me. I removed the comments as they're less clear in all black text - I want to update the "User History" worksheet when the workbook is closed to reflect changes made to the "Snapshot" worksheet. They both have the same column layout: 1 - Users, 2 - Serial Numbers, 3 - Date serial issued. Once the changes start rolling in and users are being assigned units with different serials, I want the history to show past and present serials and issue dates. Now the code:

Code Block

Option Explicit
Option Compare Text

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal wb As Workbook)

Sheets("Snapshot").Copy After:=Sheets("Transactions")

MsgBox "Please do not edit the worksheet named Snapshot(2)! It is needed by the program to automatically update the User History worksheet"

End Sub

Private Sub App_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)

X = 2
y = 4

Do While X < 100
If Sheets("Snapshot").Cells(X, 2).Value = Sheets("Snapshot (2)").Cells(X, 2).Value And Sheets("Snapshot").Cells(X, 3).Value = Sheets("Snapshot (2)").Cells(X, 3).Value Then

X = X + 1

Else
Do While Sheets("User History").Cells(X, y).Value <> ""
y = y + 1
Loop

Sheets("User History").Cells(X.y).Value = Sheets("Snapshot").Cells(X, 2).Value + " , " + Sheets("Snapshot").Cells(X, 3).Value

End If

X = X + 1

Loop

Application.DisplayAlerts = False
Sheets("Snapshot (2)").Delete
Application.DisplayAlerts = True

End Sub

I placed the above code in a class module called Class1 and then put the following in a module:

Code Block

Dim X As New Class1

Sub InitializeApp()
Set X.App = Application
End Sub

Any suggestions



Re: Using events in Excel VBA

Cringing Dragon


It would help if you could tell us in what way it doesn't work. Do the individual subs work as you want them to when run manually Is the problem only with getting them to trigger at the right times

I'm not sure why you've gone down the path of using class modules and Appplication events. It looks like what you're trying to do there is going trigger every time any workbook is opened or closed. Is that what you want

If you want a sub to trigger when a particular workbook is opened, and another to trigger when it is closed, then you can do this by using workbook events (rather than application events) which you place in the "ThisWorkbook" module of your workbook.

In the "ThisWorkbook" module a sub called "Workbook_Open()" triggers whenever that workbook is opened, and a sub called "Workbook_BeforeClose(Cancel As Boolean)" will trigger when the workbook is closed. Put your existing code into those subs, and see if that does what you need.

Note the "Cancel as Boolean" bit is there so that the event code has the ability to stop the close - if the code contains a line which sets Cancel = True, the workbook doesn't actually close.

Have a look at the help files for "Open Event" and "BeforeClose Event" for more info.







Re: Using events in Excel VBA

NemoTheBlue

Thanks for your help. Honestly, the only reason I was using class modules and application events is because I thought I'd picked up what I needed from trolling other forums and seeing threads about (possibly) similar issues. As for how it doesn't work, I'm quite perplexed. Any time I click run, or press F5, it brings up the Macros dialog box asking me to enter a macro name. Doing so and pressing Enter doesn't run the macro I've just written, but rather creates a new blank sub with the name I entered. I am, however, able to click save in the VB editor, close Excel and then reopen the app to find my code still in ThisWorkbook, but it doesn't run.

Here's how it looks now - note I've deleted the class module and the other module (module1) so now the only code is in ThisWorkbook:

Code Block

Option Compare Text


Private Sub Workbook_Open(ByVal wb As Workbook)

Sheets("Snapshot").Copy After:=Sheets("Transactions")

MsgBox "Please do not edit the worksheet named Snapshot(2)! It is needed by the program to automatically update the User History worksheet"

End Sub

Private Sub Workbook_BeforeClose(ByVal wb As Workbook, Cancel As Boolean)

X = 2
y = 4

Do While X < 100
If Sheets("Snapshot").Cells(X, 2).Value = Sheets("Snapshot (2)").Cells(X, 2).Value And Sheets("Snapshot").Cells(X, 3).Value = Sheets("Snapshot (2)").Cells(X, 3).Value Then

X = X + 1

Else
Do While Sheets("User History").Cells(X, y).Value <> ""
y = y + 1
Loop

Sheets("User History").Cells(X.y).Value = Sheets("Snapshot").Cells(X, 2).Value + " , " + Sheets("Snapshot").Cells(X, 3).Value

End If

X = X + 1

Loop

Application.DisplayAlerts = False
Sheets("Snapshot (2)").Delete
Application.DisplayAlerts = True

End Sub


Edit: I can also remove the ByVal wb As Workbook lines. Honestly, I can't even remember why I added them now! I think they were in the help file about application events...

Am I getting warmer






Re: Using events in Excel VBA

NemoTheBlue

You've inspired me! I went in and removed a few more seemingly useless phrases and then really started looking closely at the code. I almost had it perfect, and now I've got it working! I removed the "Private"s (ouch!) and "ByVal" statements, and realised that the way I had it coded, if the cells being compared did match, I was actually adding 1 to X twice, thereby skipping the next row every time it didn't find a difference. But do you know what I think the biggest problem was If you look very closely at my previous post, you'll see in the line after the little Do loop, I had a full stop between X and y in the cell coordinates instead of a comma. The dangers of typing too fast!

The finished working code is:

Code Block

Option Compare Text


Sub Workbook_Open()

Sheets("Snapshot").Copy After:=Sheets("Transactions")
Sheets("Snapshot").Select

MsgBox "Please do not edit the worksheet named Snapshot(2)! It is needed by the program to automatically update the User History worksheet"

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

X = 2
y = 4

Do While X < 100
If Sheets("Snapshot").Cells(X, 2).Value = Sheets("Snapshot (2)").Cells(X, 2).Value And Sheets("Snapshot").Cells(X, 3).Value = Sheets("Snapshot (2)").Cells(X, 3).Value Then

X = X

Else
Do Until Sheets("User History").Cells(X, y).Value = ""
y = y + 1
Loop

Sheets("User History").Cells(X,y).Value = Sheets("Snapshot").Cells(X, 2).Value + " , " + Sheets("Snapshot").Cells(X, 3).Value

End If

X = X + 1

Loop

Application.DisplayAlerts = False
Sheets("Snapshot (2)").Delete
Application.DisplayAlerts = True

End Sub

Thank you again for your post. You definitely pointed me in the right direction and gave me food for thought.