dialog box for axis minimums, maximums, and major units

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnMullins - 20 Jun 2007 13:52 GMT

I am using the macro below to set the axis minimums, maximums, and major
units for a series of charts. I would like to tie this macro to RefEdit
controls, so that when I enter the keyboard shortcut Ctrl+Shift+A in the
active chart, a dialog box opens with three RefEdit controls in it, which I
can then use to set the MinimumScale, MaximumScale, and MajorUnit. I can get
as far as creating the dialog box in the VBE editor with the three RefEdit
controls. However, I'm clueless as to how to tie the dialog box to the
macro. Any suggestions?

(By the way, I know there is a Tushar Mehta add-in that will do the min and
max, but our computer support people are funny about add-ins, and I can't
count on them not eventually deleting it, so I'd like to do my own version)

Sub Macro20()
'
' Macro20 Macro
' Macro recorded 6/20/2007 by Mullins_J
'
' Keyboard Shortcut: Ctrl+Shift+A
'
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("Sheet1!$D$1")
.MaximumScale = Range("Sheet1!$D$2")
.MajorUnit = Range("Sheet1!$D$3")
End With
ActiveChart.Deselect
End Sub

Reply to this Message

John Mansfield - 20 Jun 2007 22:54 GMT

If you're creating your own add-in, consider storing the values on a separate
worksheet within your add-in. Assuming your add-in is named "Test.xls", add
a worksheet called "Registry". Range B1 can be the maximum value, B2 the
minimum, and B3 the major unit.

You can call the userform with something like this:

Private Sub UserForm_Initialize()
UserForm1.TextBox1.Value = Workbooks("Test.xls").Worksheets _
("Registry").Range("B1").Value
UserForm1.TextBox2.Value = Workbooks("Test.xls").Worksheets
_("Registry").Range("B2").Value
UserForm1.TextBox3.Value = Workbooks("Test.xls").Worksheets
_("Registry").Range("B3").Value
End Sub

When the user form terminates, store the values with something like this:

Private Sub UserForm_Terminate()
Workbooks("Test.xls").Worksheets("Registry").Range("B1").Value = _
UserForm1.TextBox1.Value
Workbooks("Test.xls").Worksheets("Registry").Range("B2").Value = _
UserForm1.TextBox2.Value
Workbooks("Test.xls").Worksheets("Registry").Range("B3").Value = _
UserForm1.TextBox3.Value
ActiveChart.Deselect
End Sub

(take out the underscores if needed.)

(the above assumes textboxes in the user form but RefEdit controls should
allow the same types of inputs.)

Signature

John Mansfield
http://cellmatrix.net

> I am using the macro below to set the axis minimums, maximums, and major
> units for a series of charts. I would like to tie this macro to RefEdit
[quoted text clipped - 24 lines]
> ActiveChart.Deselect
> End Sub

Reply to this Message

John Mansfield - 21 Jun 2007 14:40 GMT

I forgot to add that if you are saving your file as an .xla fiel, you will
need to change the .xls references in the code to .xla.

Signature

John Mansfield
http://cellmatrix.net

> If you're creating your own add-in, consider storing the values on a separate
> worksheet within your add-in. Assuming your add-in is named "Test.xls", add
[quoted text clipped - 57 lines]
> > ActiveChart.Deselect
> > End Sub

Reply to this Message