Hello I'm new with VBA Excel. 2

[swisslog]

Hello

I'm new with VBA Excel.

Could somebody explain how it comes that this statement:

MyName = ActiveSheet.Name

does what I expect with office 97 on W2k
but does not work with Office2001 on Mac OS 9
It gives the following error:

Run-time error '32809':
"Application-defined or Object-defined error"

Many other things are working properly!
eg
MyName = ActiveWorkbook.Name
works on both Mac or PC

Variable "MyName" is declared as a string.

Thanks very much!
Bart

 

[StevePB]

Sorry this is a vague answer, but it may be to do with the Mac treating focus differently. The ActiveSheet has three possible parent objects: Application, Window or Workbook.

Try qualifying the reference to the ActiveSheet with each of these to see if it helps e.g.:

MyName1 = Application.ActiveSheet.Name

MyName2 = Windows(x).ActiveSheet.Name 'where x is the index number or name of the Window

MyName3 = Workbooks(x).ActiveSheet.Name

Good Luck - SB.

 

[swisslog]

Thank you SB

You are certainly right with "the Mac treating focus differently". I tried out all your suggestions. Unfortunately, I still cannot find my ActiveSheet.

My problem is: the VBA script inserts n new sheets (depending on a dialog box). Then, each sheet is treated independently. With the PC, no problem: Each time before the statement "AddSheet", I read the ActiveSheet in my variable. At the end, I control each sheet by it's name.

I would be so happy if I could import my script on a Mac!

Is there a way for me to find out how I can read the name of the ActiveSheet on a Mac? (All the doc. we can find here in Switzerland only treats the Wintel world!)

Thank you very much

Bart

 

[cheerio]

If I understand correctly:

a) you want to add a new worksheet
b) you want to know the name of that new sheet

The following code works on a PC - does it work on a Mac?
If it does, I would recommend you track the sheet as an object rather than by its name.

Public Sub testnewsheet()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets.Add
MsgBox ws.Name, vbInformation, "New Sheet Added"
Set ws = Nothing
End Sub

Note if there is uncertainty over the active workbook as well as the active sheet then you can replace Activeworkbook with thisworkbook if the sheet is to go into the same workbook as the VBA code.

Ken

 

[wwgmr]

What I would suggest is try the record macro button. Then try to create the steps you want it to do. This will at least let you see the code that the Mac is writing in. Then we can go from there.

I think that it uses almost the exact code.
I know that usally sheets("sheet1&quot.name = something will change name in mac like in PC.

 

[swisslog]

Yes! Wonderfull!
Cheerio: Your suggestion to work with a Worksheet object was the good one. That is of course much better than trying to work with the sheet's name. I did so because I started coding with recording macros and macros do not allways generate the best code!
The Mac do not have an "ActiveSheet". You have to "select" the sheet like you select a cell. Now I know this.

Thank you also to wwgmr: I did it that way. But when you read the generated code, the macro allways refers to a sheet name. And the name of a new sheet is not allwas the same. For example, here in Europe, most people work with a national version of Excel (french in my country). If you write a VBA script on an English version of Excel and you suppose that a new sheet is called "Sheet 1", then you will receive an error when you execute your code on a french version of Excel, because then a new sheet is called "Feuille 1".

Thank you very much to you all. I keep this forum in my favorites and hope I will be able to help somebody in the future.

Bart

 

[cheerio]

Glad to have been of help and merci beaucoup for the star.

Ken