How to acess form elements values in the Workbook_newsheet event??

[divya]

Hiiii
1.I have a form which is loaded on workbook_open event.
2.On this form There are two option buttons namely optSI and
optFPS.the user selects one and clicks Ok button
3.Now the onClick event for OK has following code
Public fps, si As Integer ' I want to know scope of
these variables
Private Sub CmdOK_Click()
If OptFPS Then
fps=1
Sheets.Add
ElseIf optSI Then
Sheets.Add
si = 1
End If
Unload Me
End Sub
4. Now when a sheet is added I want to
1. call convertmkstofps if optfps is selected
2 call convertmkstosi if optsi is selected

How can we do this??What I did is
I wrote followinfg code in the Workbook_newsheet event

Private Sub Workbook_NewSheet(ByVal Sh As Object)
If fps = 1 Then
Call convertmkstofps
ElseIf si = 1 Then
Call convertmkstosi
End If
End Sub

But this doesn't seem to work,I tried to display fps and si in
message box it doesn't show any value.
I have declared these values as public as u see in Note 3 above the
command button click event,then too its scope is limited only till the
form.

Plzz tell me how do I do this,I mean access (optfps and optsi) values
or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event

Regards
Divya

 

[Bob Phillips]

Why not just do it in the OK code, just after the sheet has been added.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[divya]

Hii Bob
Actually this is only part of the code I sent you therz
lot more things I am doing
inside the Workbook_newsheet event.Like copying the content from a
worksheet called Template which has in it all the MKS units.
So if the user selects FPS or SI and clicks OK , I insert
a new sheet which will be a copy of Template and then based on the
optionbutton selected I call the macro which replaces few cells of this
new sheet generated to FPSunits or SIunits based on the option button
selection.So I cannot afford to write in Ok button click.Kindly suggest
a way to to this.

Regards
Divya

 

[divya]

I solved it.I just made a small mistake declaring the Public variables
at wrong place,so was facing this problem.
Regards