| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to acess form elements values in the Workbook_newsheet event??
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to acess form elements values in the Workbook_newsheet event??
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" wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to acess form elements values in the Workbook_newsheet event??
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 Bob Phillips wrote: 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" wrote in message oups.com... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to acess form elements values in the Workbook_newsheet event??
I solved it.I just made a small mistake declaring the Public variables
at wrong place,so was facing this problem. Regards Divya divya wrote: 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 Bob Phillips wrote: 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" wrote in message oups.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating Chart using form elements in Excel | Excel Programming | |||
| Programmatic addition of form elements relative to a cell | Excel Programming | |||
| Pivot table with form elements | Excel Programming | |||
| user form-on open event? keydown event? | Excel Programming | |||
| Assign macros to dynamically created form elements | Excel Programming | |||
Linear Mode
