PC to Mac problem

[MEP]

I am looking for some help please with an urgent problem.

I have limited knowledge of VBA but have written a spreadsheet for my
sister that we cannot get to work.

I made the mistake of using active x controls not realising these would
not work on her Mac. I use Windows XP with Excel 2000 and she uses
MACOS10 With xl for mac 2004.

I do not have access to a mac and we live on different continents so
testing is difficult.

This was the code I used for the two command buttons.

1- to copy preformatted columns from a hidden sheet
Private Sub NewColumn_Click()

Dim ColumnNo As Integer
Dim SourceRange As Range
Dim CurrentIndex As Integer

CurrentIndex = ActiveSheet.Index
ColumnNo = 26
Set CurrentCell = Worksheets(CurrentIndex).Cells(3, ColumnNo)
While CurrentCell.Value = "Year"
ColumnNo = ColumnNo + 21
Set CurrentCell = Worksheets(CurrentIndex).Cells(3, ColumnNo)
Wend
Set CurrentCell = Worksheets(CurrentIndex).Cells(1, ColumnNo)
Set DestRange = Worksheets(CurrentIndex).Columns(ColumnNo)
Set SourceRange = Worksheets("G3 Columns").Columns("A:U")
Worksheets(CurrentIndex).Unprotect
SourceRange.Copy DestRange
Worksheets(CurrentIndex).Protect

End Sub


2. To copy a presetup hidden sheet
Private Sub NewSheet_Click()

Worksheets("NewPage").Copy Before:=Worksheets("G3 Columns")
ActiveSheet.Name = "Grade 3 (" & Str(ActiveSheet.Index) & ")"
ActiveSheet.Protect
ActiveSheet.Visible = True

End Sub

Can any one tell me how to get these to work as macros on a mac. I have
tried with a button from the forms toolbar and attaching macros but no
macro I create seems to work on my sisters mac.

 

[JE McGimpsey]

Can any one tell me how to get these to work as macros on a mac. I have
tried with a button from the forms toolbar and attaching macros but no
macro I create seems to work on my sisters mac.

Just looking at them, as long as the subs are put in a regular code
module and Private is changed to Public, attaching them to Form toolbar
buttons should work fine.

What does "no macro I create seems to work" mean, exactly? Does the code
not run? Does it produce an error?

I've got to run now, but I'll try the code on a sample book later...

 

[MEP]

I did try what you suggested about putting them in a code module and
making them public and it all still works as it should on my PC

But when my sister clicks either button they generate an error (not
sure what the error is)and debug always takes her to the first line of
code. It is like extracting teeth trying to get information about what
exactly is happening as she is barely computer literate and becomes
very confused once out of the main excel environment, hence the need
for these buttons.

For test purposes I did try recording a simple macro to copy a sheet
and attaching it to a button and then sending a workbook to her with it
in but even that macro created an error.

Sub NewSheet()
'
' NewSheet Macro
' Macro recorded 14/07/2005 by Mary
''
Sheets("NewPage").Visible = True
Sheets("NewPage").Select
Sheets("NewPage").Copy Before:=Sheets(3)
Sheets("NewPage").Select
ActiveWindow.SelectedSheets.Visible = False

End Sub

Is it possible that her Excel setup is faulty in some way? Or am I just
missing something very simple here?

I greatly appreciate your help here as I am pulling my hair out.

 

[J Laroche]

MEP wrote on 2005/07/15 09:09:

I made the mistake of using active x controls not realising these would
not work on her Mac. I use Windows XP with Excel 2000 and she uses
MACOS10 With xl for mac 2004.

The macros work perfectly well for me, so your hunch about ActiveX controls
is probably right.

I don't have a PC to test it right now, but I think you can use Forms
controls that are cross-platform. Create them with these instructions, and
if doesn't work for you your sister can certainly do it with a bit of
patience.

Unprotect the sheet where the buttons are to be located (Tools, Protection,
Unprotect Sheet). Show the Forms toolbar (View, Toolbars, Forms). On it,
click the Button button (second row, second column in Office v.X) and drag
the outline of a new button on the worksheet. When the mouse button is
released a dialog will appear. Choose a macro to assign to the button and
click OK. Once it's done, click on the new button's text to change it to be
meaningful for the purpose.

Note that a button's text can only be changed if there's a checkered border
around the button. It there isn't, ctrl-click on the button and select Edit
text from the pop-up menu.

Good luck

JL
Mac OS X 10.3.9, Office v.X 10.1.6

 

[JE McGimpsey]

Is it possible that her Excel setup is faulty in some way? Or am I just
missing something very simple here?

It's certainly possible that the setup is faulty, but it seems unlikely.
Without knowing what the error is, it's impossible to know.

I did try the macros on a test workbook, and they work fine attached to
forms toolbar command buttons.

Perhaps you can get your sister to read you the error, or type it into
an email message. She could even press CMD-SHIFT-3 with the error
showing to take a picture of her desktop which she could then send to
you.