Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Some time ago, in a series of threads, I learned that if a program is
always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Your event code must be on the userform, so when a control is clicked or
changed etc. the private sub will act on these events from these you can call functions or other subs in external modules that carry out the steps required. Private Sub btnCancel_Click Call mySub ' in another module myValue = myFunction ' in another module End Sub If you wish to act on the control on the userform, then that code could be placed in the userform or the name of the userform passed to the sub routine as an object parameter, this method allows code to be reusable for different forms, something like.... Dim myForm as Object Private Sub btnCancel_Click Set myForm = Me.Name Call mySub (myForm) End Sub -- Cheers Nigel "davegb" wrote in message oups.com... Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Nigel wrote: Your event code must be on the userform, so when a control is clicked or changed etc. the private sub will act on these events from these you can call functions or other subs in external modules that carry out the steps required. Private Sub btnCancel_Click Call mySub ' in another module myValue = myFunction ' in another module End Sub If you wish to act on the control on the userform, then that code could be placed in the userform or the name of the userform passed to the sub routine as an object parameter, this method allows code to be reusable for different forms, something like.... Dim myForm as Object Private Sub btnCancel_Click Set myForm = Me.Name Call mySub (myForm) End Sub -- Cheers Nigel Thanks for your reply, Nigel. Unfortunately, you didn't answer either of my questions. Can anyone answer my questions? "davegb" wrote in message oups.com... Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
i can help you with the 1st question, if i understand what you want.
i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
ok. what do you mean by "access"?
being able to type in the form? or being able to retrieve information from the form? susan davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Susan wrote: ok. what do you mean by "access"? being able to type in the form? or being able to retrieve information from the form? susan I mean showing the form for the user to select options, type in text, select a cell or range, make a selection from a combo box, etc. Any of the things that an end user would do in a userform. Does the userform have to be called from code in a module, or is there any other way to show a userform? davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
ahhhh..... light dawns on marble-head.
yes, a userform has to be called from code within a module. you can either use an auto_open sub in a module, in which case the userform will automatically pop up when the file opens up, or you can put a different sub in a module. xxxxxxxxx Public Sub auto_open() Load Userform1 Userform1.Show End Sub xxxxxxxxxxxxxxx if you want the user to choose when it loads, you can put an autoshape in the spreadsheet, with text on it like "Click this to add a person."... then right-click the autoshape & choose "Assign Macro." Then when they click the autoshape the userform will pop up. you will also need a "Userform1_Initialize()" sub in your userform module, like this: xxxxxxxxxxxxxx Sub frmSched_initialize() Dim oControl As Control For Each oControl In Me.Controls If TypeOf oControl Is msforms.TextBox Then oControl.Value = "" ElseIf TypeOf oControl Is msforms.OptionButton Then oControl.Value = False End If Next oControl MultiPage1("pgSchedule").txtClient.SetFocus End Sub xxxxxxxxxxxxxxxxxxxx this initialization sub sets all my textboxes blank & option buttons as false, then sets the cursor in the txtClient textbox. glad i could finally answer (at least one) of your questions. susan davegb wrote: Susan wrote: ok. what do you mean by "access"? being able to type in the form? or being able to retrieve information from the form? susan I mean showing the form for the user to select options, type in text, select a cell or range, make a selection from a combo box, etc. Any of the things that an end user would do in a userform. Does the userform have to be called from code in a module, or is there any other way to show a userform? davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Susan wrote: ahhhh..... light dawns on marble-head. LOL! It's more about my phrasing of the question than your marbles, or lack thereof. yes, a userform has to be called from code within a module. Thank-you, thank-you, thank-you! you can either use an auto_open sub in a module, in which case the userform will automatically pop up when the file opens up, or you can put a different sub in a module. xxxxxxxxx Public Sub auto_open() Load Userform1 Userform1.Show End Sub xxxxxxxxxxxxxxx if you want the user to choose when it loads, you can put an autoshape in the spreadsheet, with text on it like "Click this to add a person."... then right-click the autoshape & choose "Assign Macro." Then when they click the autoshape the userform will pop up. you will also need a "Userform1_Initialize()" sub in your userform module, like this: xxxxxxxxxxxxxx Sub frmSched_initialize() Dim oControl As Control For Each oControl In Me.Controls If TypeOf oControl Is msforms.TextBox Then oControl.Value = "" ElseIf TypeOf oControl Is msforms.OptionButton Then oControl.Value = False End If Next oControl MultiPage1("pgSchedule").txtClient.SetFocus End Sub xxxxxxxxxxxxxxxxxxxx this initialization sub sets all my textboxes blank & option buttons as false, then sets the cursor in the txtClient textbox. glad i could finally answer (at least one) of your questions. susan davegb wrote: Susan wrote: ok. what do you mean by "access"? being able to type in the form? or being able to retrieve information from the form? susan I mean showing the form for the user to select options, type in text, select a cell or range, make a selection from a combo box, etc. Any of the things that an end user would do in a userform. Does the userform have to be called from code in a module, or is there any other way to show a userform? davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
:) i'm glad it's starting to work!
can you post your final code (when you're done) so in the future when somebody's looking for the answer to the same question, they can see what/how you did it? thanks susan davegb wrote: Susan wrote: ahhhh..... light dawns on marble-head. LOL! It's more about my phrasing of the question than your marbles, or lack thereof. yes, a userform has to be called from code within a module. Thank-you, thank-you, thank-you! you can either use an auto_open sub in a module, in which case the userform will automatically pop up when the file opens up, or you can put a different sub in a module. xxxxxxxxx Public Sub auto_open() Load Userform1 Userform1.Show End Sub xxxxxxxxxxxxxxx if you want the user to choose when it loads, you can put an autoshape in the spreadsheet, with text on it like "Click this to add a person."... then right-click the autoshape & choose "Assign Macro." Then when they click the autoshape the userform will pop up. you will also need a "Userform1_Initialize()" sub in your userform module, like this: xxxxxxxxxxxxxx Sub frmSched_initialize() Dim oControl As Control For Each oControl In Me.Controls If TypeOf oControl Is msforms.TextBox Then oControl.Value = "" ElseIf TypeOf oControl Is msforms.OptionButton Then oControl.Value = False End If Next oControl MultiPage1("pgSchedule").txtClient.SetFocus End Sub xxxxxxxxxxxxxxxxxxxx this initialization sub sets all my textboxes blank & option buttons as false, then sets the cursor in the txtClient textbox. glad i could finally answer (at least one) of your questions. susan davegb wrote: Susan wrote: ok. what do you mean by "access"? being able to type in the form? or being able to retrieve information from the form? susan I mean showing the form for the user to select options, type in text, select a cell or range, make a selection from a combo box, etc. Any of the things that an end user would do in a userform. Does the userform have to be called from code in a module, or is there any other way to show a userform? davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
running program from userform
Susan wrote: :) i'm glad it's starting to work! can you post your final code (when you're done) so in the future when somebody's looking for the answer to the same question, they can see what/how you did it? thanks susan Susan, Normally I would be happy to, but in this case, there is no final code to post, it's where the code is that's relevant, not the code itself. Thanks for your help. Dave davegb wrote: Susan wrote: ahhhh..... light dawns on marble-head. LOL! It's more about my phrasing of the question than your marbles, or lack thereof. yes, a userform has to be called from code within a module. Thank-you, thank-you, thank-you! you can either use an auto_open sub in a module, in which case the userform will automatically pop up when the file opens up, or you can put a different sub in a module. xxxxxxxxx Public Sub auto_open() Load Userform1 Userform1.Show End Sub xxxxxxxxxxxxxxx if you want the user to choose when it loads, you can put an autoshape in the spreadsheet, with text on it like "Click this to add a person."... then right-click the autoshape & choose "Assign Macro." Then when they click the autoshape the userform will pop up. you will also need a "Userform1_Initialize()" sub in your userform module, like this: xxxxxxxxxxxxxx Sub frmSched_initialize() Dim oControl As Control For Each oControl In Me.Controls If TypeOf oControl Is msforms.TextBox Then oControl.Value = "" ElseIf TypeOf oControl Is msforms.OptionButton Then oControl.Value = False End If Next oControl MultiPage1("pgSchedule").txtClient.SetFocus End Sub xxxxxxxxxxxxxxxxxxxx this initialization sub sets all my textboxes blank & option buttons as false, then sets the cursor in the txtClient textbox. glad i could finally answer (at least one) of your questions. susan davegb wrote: Susan wrote: ok. what do you mean by "access"? being able to type in the form? or being able to retrieve information from the form? susan I mean showing the form for the user to select options, type in text, select a cell or range, make a selection from a combo box, etc. Any of the things that an end user would do in a userform. Does the userform have to be called from code in a module, or is there any other way to show a userform? davegb wrote: Susan wrote: i can help you with the 1st question, if i understand what you want. i'm still a learner myself, so bear with me. i do (what i think you want) all the time.......... in a separate module, called usually GlobalMods, i declare all my variables & stuff as PUBLIC. then i write any mini-routines in there, too. then, in the userform module, i write my main macro connected with the click() event button on the macro. when i need to call the mini-routine in the middle of the main macro, i just call it & it switches over there & runs. example: xxxxxxxxxxxxxxxxxxx Global Mods: Public r as Range Public txtFee as Control public sub print() application.displayalerts=false activeworksheet.printout activewindow.close application.displayalerts=true end sub -------------------------- userform module: Sub OK_Click() blah blah blah whatever you want to do Call Print end sub xxxxxxxxxxxxxxxxxxxxxxx you can "call" as many mini-subs as you want from the main macro. i hope this is what you were talking about. susan Thanks for your reply. I guess I'm not making myself clear. I know I can call a macro in a module from a userform. I know I can show a userform from a macro in a module. What I'm asking is, do I have to have at least some code in a module to access the userform? Is there a way to directly go to a userform without going through some code in a module? I hope that's more clear on question 1. I'll repost on question 2. davegb wrote: Some time ago, in a series of threads, I learned that if a program is always run in conjunction with a userform, the code should all be in the userform, not in a module. I was too far along with the previous program to re-write it all to be in the userform. Now I'm writing another program that will only be run with the userform (uf1021Mid), so I'm trying to write the code entirely in the userform. So far, I have 2 problems. One is, how do I initiate a program in the userform? Normally, when I initiate a program, I call it's name from the macro menu or from a tool to which it's been assigned. Since the code in the userform already has names like "Private Sub btnCancel_Click()", I don't think I can call the program from those names. So how do I access the macro? Maybe I misunderstood the previous posts and there always has to be at least the Sub name and a userform call in a module? Second problem in testing to see if the refedit in the userform is returning a range. I'm using the following code, the test code being from Walkenbach's book: Private Sub OKButton_Click() Dim wbExtr As Workbook Dim wsRef As Worksheet 'wks where Top 10 list is stored Dim wsExtFrom As Worksheet 'Wks where data is extracted from Dim wsTop As Worksheet 'wks where new table goes Dim oWS As Object Dim rCopy As Range Dim rCell As Range 'each cell in rRef Dim rRef As Range 'Range on wsRef where current CtyLst is Dim rExtrFromStrt As Range Dim rExtrFrom As Range 'range in Src sheet Where cty names are Dim rRefRow As Range Dim rExtrFromEnd As Range 'Dim rColHdr As Range Dim rHdrStrt As Range Dim rHdrEnd As Range Dim s1stCtyName As String Dim sUCrCell As String Dim sCtyName As String Dim sHeader As String Dim lExtrFromCol As Long 'CtyCol in Src sht Dim lExtrToCol As Long Dim lTopRow As Long Dim lCopyRow As Long Dim lBOS10Row As Long Dim lBOS21Row As Long Dim lStrDif As Long Dim lMid3Row As Long Dim lBOS3Row As Long Dim lRefRow As Long Dim lExtrFromStrt As Long Dim lColHdrCount As Long Dim lLastCol As Long Dim lLastRow As Long Dim bHdr As Boolean Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsExtFrom = ActiveSheet Set wbExtr = ActiveWorkbook lTopRow = 2 lBOS10Row = 14 lBOS21Row = 25 lMid3Row = 14 lBOS3Row = 27 'Test is Mark Top 10 workbook is active If ThisWorkbook.Name = wbExtr.Name Then MsgBox "You have selected the workbook that contains the macro." & _ Chr(13) & "Please click Ok and select the correct workbook and " & _ Chr(13) & "worksheet and restart the macro.", vbOKOnly Exit Sub End If 'TEST FOR SHEET NAMED "Top" For Each oWS In wbExtr.Sheets If oWS.Name = "Top" Then If MsgBox("A worksheet named Top already exists in this workbook." _ & Chr(13) & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next On Error Resume Next 'Set rColHdr = ActiveSheet.Range(reDataStrt.Text) Set uf1021Mid.rColHdr = Range(reDataStrt.Text) If Error < 0 Then MsgBox "Invalid range selection, please select the starting range again." On Error GoTo 0 Exit Sub End If uf1021Mid.Hide End Sub No matter what I enter in the refedit control, I get the error message. Any ideas? Thanks as always. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running program | Excel Programming | |||
Running a external Program | Excel Programming | |||
A program running on different Excel Versions | Excel Programming | |||
Stop a VB program from running | Excel Programming | |||
Running Program on a Mac | Excel Programming |