Call Specific worksheet function from ThisWorkbook module

[losekiss431a]

Hi!

Am trying to run the following code from the ThisWorkbook module. However, I'm faced with the error: "Compile Error. Method or Data member not found."

Please Login or Register  to view this content.

It calls the code from all the sheets (other than the Settings). They have the same code as per below.

Please Login or Register  to view this content.

Please help. Thanks

[xladept]

Try:

Please Login or Register  to view this content.

Late Correction:


I should have said -

Please Login or Register  to view this content.

But it wouldn't have solved anything anyway

[skywriter]

Never mind. xladept is on it.

[losekiss431a]

Still the same error. The sub routine runs perfectly fine by itself when called from within the worksheet. It only faces this error when called from the ThisWorkbook module. Any help?

[skywriter]

Okay, just to make sure we're on the same page.

This sub routine.

Please Login or Register  to view this content.

Is event driven, it runs from the AfterSave event, which I've never used but it seems self explanatory, you save the workbook this code runs.

Is that your understanding or are you trying to call the AfterSave sub routine by using code somewhere else in your workbook?

[skywriter]

After doing some experimentation I found the following.
Since you have defined ws as a worksheet, then as soon as you type ws. you get Intellisense. You will notice the sub routine you are trying to call is not one of your choices.
You can call this sub routine by simply having the name on a line.

Please Login or Register  to view this content.

Looping through the worksheets in the code as you are doing is doing nothing for you. I suspect you think you are passing a value or something else and this is the reason for looping through the code. I see you have a variable sLastEdit, is that variable defined by each sheet somehow and you are wanting to loop the sheets to change the variable each time you call the UpdateLastUpdate routine?

[skywriter]

So have a look at the workbook below.

I have this code in ThisWorbook

Please Login or Register  to view this content.

This code in a standard module.

Please Login or Register  to view this content.

You can see in the workbook I have values in A1 on each of the three sheets and when you save the worksheet I get three message boxes all with the values from A1 in each of those sheets.
Hopefully this will help you figure out what you need to do.

Good Luck.

[losekiss431a]

Hey. Thanks for your help.

Few things though, I'm trying not to write the variable to the worksheet to increase the efficiency. The real worksheets goes in excess of 100000 rows. Here is a simplified file to explain what I'm trying to do.LoveKiss431a.xlsm

[skywriter]

There's three empty sheets.

[romperstomper]

Declare ws as Object. The base Worksheet class doesn't have the routine you are trying to call, hence the error.

[losekiss431a]

@romperstomper, you're a genius. I never thought about the Worksheet class.

Sidenote: Do I have to clear the Object from memory or would it clear automatically at the end of the Sub routine?

Thanks guys :D

[romperstomper]

It will automatically clear.

[skywriter]

@romperstomper, you're a genius. I never thought about the Worksheet class.

Sidenote: Do I have to clear the Object from memory or would it clear automatically at the end of the Sub routine?


Thanks guys :D

I still don't know what you were doing or how you incorporated romperstomper's suggestion.

Can you share the final code with us?

[losekiss431a]

@Skywriter,

Here's the final code.

Please Login or Register  to view this content.

As the issue I was facing had nothing to do with any front-end UI, I left them blank. Do take a look at the named range and the VBA code. Think you'll get it Let me know if you want me to explain further.