Call Worksheet_Activate Event from procedure possible?

[ShieBoon]

Hi guys, is it possible to call a Worksheet's activate event from a procedure? Or maybe, refresh a worksheet from a procedure. i'm using excel 2003.

Thanks
Shie Boon

 

[Smitty]

Sure, you can activate the sheet. If you use ScreenUpdating = False no one will see it anyway.

Although if you're trying to reuse some code, I'd put the code in a general module, then call it from the Activate event, and wherever else you need it, instead of recreating it.

It'll help if you post the code that you've got now, and describe what you're trying to accomplish.

 

[xenou]

See Erik's reply in this post:
http://www.mrexcel.com/forum/showthread.php?t=295373

In short, if you change the worksheet_activate from Private to Friend, it will be accessible from other procedures in your project even if they are not in the sheet code module itself.

 

[ShieBoon]

Hi smitty and xenou, thank you both for the quick response!

Ok guys, here's how it's like. It's quite long so do bear with me. Hope it will be clear enough for you to get the picture.

(Alright here i go)
The users use my excel workbook for inserting records and viewing records of the database. Whenever they view records, in general, it will be a filtered version of the database.

So it goes like this, Main Page -> Press Button -> Filtered View of database
In the filtering code, i had the autofilter run from the start of the worksheet to the last record of data in the worksheet. This is to prevent data inconsistencies.

Unfiltered Database Example
Row 1 Apple
Row 2 Pear
Row 3 Apple
Row 4 Pear
Row 5
Row 6

Filtered Database Example (Filter for Apple only)
Row 1 Apple
Row 3 Apple
Row 5

Alright back to the topic. All buttons in the main page leads to a worksheet("MasterList") that shows the filtered view of the database.

In this ("MasterList") sheet, i also allow users to insert records by clicking a 'Insert New Record' button. The codes within this button needs to know the row number after the last record, which is row 5 in the preceeding example. (Coming to the end, bear with me!) So in the Worksheet_Activate event of ("MasterList"), i added codes to calculate the row number after the last record. Now here's the problem.

When the user clicks the button from the main page, the row number after the last record is calculated and stored as variable NextRecord. But thing is, i want to allow flexibility for the user to insert records without clicking the "Insert New Record button". That means, they can just type in data at row number 5.

Let's say a user has 2 records to enter. He clicks the button from main page and is rirected to ("MasterList"). NextRecord is calculated (5). And then they choose to enter the first record without clicking the button. NextRecord is still (5). But the row after the last record is supposed to be 6.

Then the user chooses to add the 2nd record by clicking the 'Insert New Record' button. And then, the codes in the 'Insert New Record' button would use (5) instead of 6. And overwriting of Row number 5 would be done.

This is why i need to call ("MasterList")'s Worksheet_Activate event, to recalculate NextRecord, in the sub procedure of the button 'Insert New Record'!

Currently, my codes in the sub procedure of button 'Insert New Record' are the following:
Sub Insert_Record_Form()
'
' InsertRecordForm Macro
' Macro recorded 09/05/2011 by A9990084
'
' Keyboard Shortcut: Ctrl+y
'
Worksheets("Main").Visible = True
Worksheets("MasterList").Visible = False
Worksheets("MasterList").Visible = True
Worksheets("Main").Visible = False
Load Insert_New_Record_Form
Insert_New_Record_Form.MultiPage_Insert.Value = 0
Insert_New_Record_Form.Show

End Sub

' The above codes in blue are to so called 'Reactivate' the MasterList. It does works, but there's a slight flash of the "Main" page whenever i press the button. It would be weird to the users.

So the qn is, do you guys have any knowledge of codes that would 'reactivate' MasterList without having to switch between different sheets?

Thank you!!

 

[ShieBoon]

To add on, basically, the unfiltered MasterList IS the database itself.

 

[Smitty]

Short of time, I'm just focusing on your "blue" code:

Worksheets("Main").Visible = True
Worksheets("MasterList").Visible = False
Worksheets("MasterList").Visible = True
Worksheets("Main").Visible = False

Which is entirely unnecessary. Take a look at what I mentioned about ScreenUpdating.

I'm heading out of town for a few days, but I'm sure that xenou will get you squared away in short order.

 

[ShieBoon]

Alright, thanks loads smitty~

 

[ShieBoon]

Hi yanfeng, i don't quite understand what you are referring to. Could you explain to me? Apologies.

Thanks

 

[RoryA]

I think it's spam - ignore it.

Why not use Smitty's original suggestion - move your code from the activate event to a normal module and then call that routine from both places.

 

[ShieBoon]

Hi rorya!

Hm you mean.. call the activate event from the procedure?
If you've read the long long post i posted above, haha, here is the code of the sub procedure of button 'Insert New Record'

Sub Insert_Record_Form()
'
' InsertRecordForm Macro
' Macro recorded 09/05/2011 by A9990084
'
' Keyboard Shortcut: Ctrl+y
'
Worksheets("Main").Visible = True
Worksheets("MasterList").Visible = False
Worksheets("MasterList").Visible = True
Worksheets("Main").Visible = False
Load Insert_New_Record_Form
Insert_New_Record_Form.MultiPage_Insert.Value = 0
Insert_New_Record_Form.Show

End Sub

I tried something like Worksheets("MasterList").Activate. But i think it didn't work because the masterlist sheet was already the active sheet. So all i could do was to shift the focus to another worksheet (main) and back to 'reactivate' the Worksheet_Activate event.