returning values application.dialogs(...).show

[Pringles.]

is there any way to maybe return any other (besides the boolean) value from,
for example Application.Dialogs(xlDialogFormatNumber)?

[Dave Peterson]

Select a cell (on a new temporary worksheet???)
show the dialog
rectrieve the numberformat for that cell
delete the worksheet

Pringles. wrote:
>
> is there any way to maybe return any other (besides the boolean) value from,
> for example Application.Dialogs(xlDialogFormatNumber)?

--

Dave Peterson

[Jon Peltier]

Dave -

Excellent. I had the same question, and this little sequence works great.

Function GetFormat(Optional sDefault As String = "")
Dim b As Boolean
Dim s As String
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = Workbooks.Add.Worksheets(1)

If Len(sDefault) > 0 Then
ActiveCell.NumberFormat = sDefault
End If

b = Application.Dialogs(xlDialogFormatNumber).Show

s = ActiveCell.NumberFormat

Application.DisplayAlerts = False
ws.Parent.Close False
Application.DisplayAlerts = True

Application.ScreenUpdating = True

GetFormat = s

End Function

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dave Peterson" wrote:

> Select a cell (on a new temporary worksheet???)
> show the dialog
> rectrieve the numberformat for that cell
> delete the worksheet
>
> Pringles. wrote:
> >
> > is there any way to maybe return any other (besides the boolean) value from,
> > for example Application.Dialogs(xlDialogFormatNumber)?
>
> --
>
> Dave Peterson
>