Userform

[Bill]

Hello All,
I need to have a macro that works with either PC or Mac. I had a number of
userforms I made on the PC. When I try them on a Mac, they don't look the
same: not showing all the labels, etc. Is there a way to make a userform on
the PC that will show correctly on the Mac, or do I have to rework each
userform?

Thanks,

Bill

 

[Bob Greenblatt]

Hello All,
I need to have a macro that works with either PC or Mac. I had a number of
userforms I made on the PC. When I try them on a Mac, they don't look the
same: not showing all the labels, etc. Is there a way to make a userform on
the PC that will show correctly on the Mac, or do I have to rework each
userform?

Thanks,

Bill

You will probably have to rework each user form. Make sure you are using a
font available on both systems, like Ariel, and do not use any Active-X
controls on the form.

I do this all the time, and find that it is a little easier to build the
form on the Mac and then see if it looks right on the PC. If not I write a
little bit of platform dependent code that runs at form initialization that
"tweaks" the position and sizes of the objects on the form.

 

[Bill]

Thanks for feedback Bob. One more, there appears to be a difference in how
the Mac Excel handles color for points on charts. Can you explain that
difference?

Thanks,

Bill

 

[Bob Greenblatt]

Thanks for feedback Bob. One more, there appears to be a difference in how
the Mac Excel handles color for points on charts. Can you explain that
difference?

Thanks,

Bill

Give me some more information. What kind of chart? What differences are you
seeing?

 

[JE McGimpsey]

Hello All,
I need to have a macro that works with either PC or Mac. I had a number of
userforms I made on the PC. When I try them on a Mac, they don't look the
same: not showing all the labels, etc. Is there a way to make a userform on
the PC that will show correctly on the Mac, or do I have to rework each
userform?

In addition to Bob's excellent advice, I've occasionally taken to
scaling userforms in my add-ins, using the _Initialize event to change
the layout/size/font size/etc of each control on each page of a
userform, using conditional compilation and different scaling factors
for MacOffice and PCOffice. All the parameters are kept in a table in a
hidden worksheet in the add-in to reduce the amount of code required. It
can be a pain, but it can make a very slick and customizable application.

One implementation let the user change fonts/font sizes on a multi-page
user form, and the buttons/comboboxes/labels all resized in a visually
pleasing way, expanding or contracting the form as appropriate. In
addition, there were a few additional options for users of WinXL00+ that
weren't in the MacXL or XL97 forms.

 

[JE McGimpsey]

One more, there appears to be a difference in how the Mac Excel
handles color for points on charts. Can you explain that difference?

That's rather an open-ended question - can you be more specific?

 

[Bill]

I make a chart on a worksheet. Then I recorded a macro to change the color
of a point to red. The code is:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlDiamond
.MarkerSize = 5
.Shadow = False
End With

When I run this code on the Mac, it does not change the color. Why is that?

Thanks,

Bill

 

[Bill]

By the way, that code does work with Excel X, but Excel 2004 (using their 30
day trial).

 

[Bob Greenblatt]

I make a chart on a worksheet. Then I recorded a macro to change the color
of a point to red. The code is:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlDiamond
.MarkerSize = 5
.Shadow = False
End With

When I run this code on the Mac, it does not change the color. Why is that?

Thanks,

Bill

I do not know. It works fine for me. What kind of chart? How many points? Do
you get any errors?

 

[dk_]

You will probably have to rework each user form. Make sure you are using a
font available on both systems, like Ariel, and do not use any Active-X
controls on the form.

I do this all the time, and find that it is a little easier to build the
form on the Mac and then see if it looks right on the PC. If not I write a
little bit of platform dependent code that runs at form initialization that
"tweaks" the position and sizes of the objects on the form.

Bob,

Could you/would you post some code that will tweak the form for the Mac
display? Sounds great.

-Dennis

 

[Bob Greenblatt]

Bob,

Could you/would you post some code that will tweak the form for the Mac
display? Sounds great.

-Dennis

Dennis, I have no specific code for this. Depending on your form, some of
the objects may have to be repositioned or resized slightly for each
platform. I'm sorry, but there is no short cut for displaying it on one
platform and seeing how it looks. Then tweaking it for the platform and
recording what you changed and by how much. Then do the same thing on the
other platform.

 

[dk_]

If I understand you correctly, you mainly build a userform on one
platform (a Mac for example), then on the other platform (a Windows
machine), you turn the Macro recroder 'on', and then record the tweaks
that are necessary to make the userform display right on the Windows
machine? Is that what you do? If so, that makes sense to me.

If the above is mostly right, then what is the code that the Macro can
use to know that the Macro should be run. In other words how does the
Macro trigger itself to run when the file is opened in the appropriate
platform (the Windows machine, or vise versa)?

Thanks for your ideas.

-Dennis

 

[JE McGimpsey]

If I understand you correctly, you mainly build a userform on one
platform (a Mac for example), then on the other platform (a Windows
machine), you turn the Macro recroder 'on', and then record the tweaks
that are necessary to make the userform display right on the Windows
machine? Is that what you do? If so, that makes sense to me.

I've done this, I've also used hidden sheets in an add-in to specify
position, size, font size, etc. for each control in a table, then read
the table on the userform intialize event. Doing this is *much* more
flexible, especially if you start getting into localization or other
run-time modifications.

If the above is mostly right, then what is the code that the Macro can
use to know that the Macro should be run. In other words how does the
Macro trigger itself to run when the file is opened in the appropriate
platform (the Windows machine, or vise versa)?

One way is to use conditional compilation. For example, with a userform,
MyUserForm containing some controls, including two button controls,
btnOK and btnClose:

Dim frmMyForm As MyUserForm

Set frmMyForm = New MyUserForm
Load frmMyForm
#If Mac Then
ModifyMyUserFormForMac frmMyForm
#End If
frmMyForm.Show

....


Public Sub ModifyMyUserFormForMac(byRef frm As MyUserForm)
Const cnBtnHeight As Long = 20
Const cnBtnWidth As Long = 60
With frm
With .btnClose
.Left = 100&
.Top = 200&
.Width = cBtnWidth
.Height = cBtnHeight
End With
With .btnOK
.Left = 175&
.Top = 200&
.Width = cBtnWidth
.Height = cBtnHeight
End With
'etc.
End With
End Sub

 

[Bob Greenblatt]

I've done this, I've also used hidden sheets in an add-in to specify
position, size, font size, etc. for each control in a table, then read
the table on the userform intialize event. Doing this is *much* more
flexible, especially if you start getting into localization or other
run-time modifications.

The macro recorder will not record when a dialog box is being shown. I meant
record with a pencil and paper what the new sizes and locations need to be.
Then implement that in code. JE's response uses conditional compilation to
generate the correct version for the platform. As a matter of style, I use
a run time test to accomplish the same thing.

 

[dk_]

JE and Bob Greenblatt,

JE, thank you for posting your sample code. I will save this for a
future project after I've learned a bit more about Excel. Also, since
Bob explained that the macro recorder does not record the changes when
editing a user form, I will definately have to wait a bit for this
project. ...But at least I now understand the idea of how to get it done.

Thank you.

-Dennis