Run Time Error on trying to save workbook
in [Programming]
|
Prev: RefEdit in UDF ?
Next: Found the answer...
From: Brian on 2 Feb 2010 19:45 This code worked perfect when it was in the User Form Code Window, but I moved it to a module and I get a Run Time Error "424' Object Required Code in the User Form Window -------------------------------------------------- '******************************************************* 'Save Installer Forms 11 Control Button 'Located in M3_Save_Workbook '******************************************************* Private Sub Save_Installer_Forms_11_Click() Call Save_Installer_Forms End Sub Code Located in the module: Declarations ---------------------------------------- Dim strFile As String Dim fileSaveName As Variant Dim myMsg As String 'Save Installer Forms 11 Control Button Sub Save_Installer_Forms() ' Dim strFile As String ' Dim fileSaveName As Variant ' Dim myMsg As String strFile = "FORMS " & CLLI_Code_1.Value _ "This Block Turns Yellow" & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value fileSaveName = Application.GetSaveAsFilename _ (InitialFileName:=strFile, _ fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") If fileSaveName <> False Then ActiveWorkbook.SaveAs Filename:= _ fileSaveName, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False Else MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the Installer Form." & vbCrLf & _ "Installer Form was not Saved.", _ Title:="C.E.S." End If End Sub
From: OssieMac on 2 Feb 2010 20:24 Hi Brian, When the code is in the Userform module, the system knows that the controls belong to the userform by default. when you move it out of the userform module, you have to tell the syetem where the controls are. Looking at your code the following appear to be references to controls on the userform. CLLI_Code_1.Value TEO_No_1.Value CES_No_1.Value TEO_Appx_No_2.Value You need to prefix them with the userform name and a dot like the following. Userform1.CLLI_Code_1.Value -- Regards, OssieMac "Brian" wrote: > This code worked perfect when it was in the User Form Code Window, but I > moved it to a module and I get a Run Time Error "424' Object Required > > Code in the User Form Window > -------------------------------------------------- > '******************************************************* > 'Save Installer Forms 11 Control Button > 'Located in M3_Save_Workbook > '******************************************************* > > Private Sub Save_Installer_Forms_11_Click() > > Call Save_Installer_Forms > > End Sub > > > Code Located in the module: > > Declarations > ---------------------------------------- > Dim strFile As String > Dim fileSaveName As Variant > Dim myMsg As String > > > 'Save Installer Forms 11 Control Button > Sub Save_Installer_Forms() > > ' Dim strFile As String > ' Dim fileSaveName As Variant > ' Dim myMsg As String > > strFile = "FORMS " & CLLI_Code_1.Value _ "This Block Turns Yellow" > & Space(1) & TEO_No_1.Value _ > & Space(1) & CES_No_1.Value _ > & Space(1) & TEO_Appx_No_2.Value > > fileSaveName = Application.GetSaveAsFilename _ > (InitialFileName:=strFile, _ > fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") > > If fileSaveName <> False Then > ActiveWorkbook.SaveAs Filename:= _ > fileSaveName, _ > FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ > CreateBackup:=False > Else > MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the > Installer Form." & vbCrLf & _ > "Installer Form was not Saved.", _ > Title:="C.E.S." > > End If > > End Sub
From: Dave Peterson on 2 Feb 2010 21:03 Another way to do it is to pass the string from the calling procedure to the called procedure. You could pass it the pieces or concatenate the string first. Or use a combination... Option Explicit Private Sub Save_Installer_Forms_11_Click() dim myStr as string mystr = "FORMS " & Me.CLLI_Code_1.Value _ & Space(1) & me.TEO_No_1.Value _ & Space(1) & me.CES_No_1.Value _ & Space(1) & me.TEO_Appx_No_2.Value 'I concatenated the first string and passed the second directly Call Save_Installer_Forms(strfile:=mystr, EngName:=Me.Engineer_2.Value) End Sub Sub Save_Installer_Forms(StrFile as string, EngName as string) Dim fileSaveName As Variant fileSaveName = Application.GetSaveAsFilename _ (InitialFileName:=strFile, _ fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") If fileSaveName <> False Then ActiveWorkbook.SaveAs Filename:=fileSaveName, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False Else MsgBox prompt:=EngName & vbLf & _ "You canceled saving the Installer Form." & vbCrLf & _ "Installer Form was not Saved.", _ Title:="C.E.S." End If End Sub Do you work for ALU or Goodman? Just curious. Brian wrote: > > This code worked perfect when it was in the User Form Code Window, but I > moved it to a module and I get a Run Time Error "424' Object Required > > Code in the User Form Window > -------------------------------------------------- > '******************************************************* > 'Save Installer Forms 11 Control Button > 'Located in M3_Save_Workbook > '******************************************************* > > Private Sub Save_Installer_Forms_11_Click() > > Call Save_Installer_Forms > > End Sub > > Code Located in the module: > > Declarations > ---------------------------------------- > Dim strFile As String > Dim fileSaveName As Variant > Dim myMsg As String > > 'Save Installer Forms 11 Control Button > Sub Save_Installer_Forms() > > ' Dim strFile As String > ' Dim fileSaveName As Variant > ' Dim myMsg As String > > strFile = "FORMS " & CLLI_Code_1.Value _ "This Block Turns Yellow" > & Space(1) & TEO_No_1.Value _ > & Space(1) & CES_No_1.Value _ > & Space(1) & TEO_Appx_No_2.Value > > fileSaveName = Application.GetSaveAsFilename _ > (InitialFileName:=strFile, _ > fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") > > If fileSaveName <> False Then > ActiveWorkbook.SaveAs Filename:= _ > fileSaveName, _ > FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ > CreateBackup:=False > Else > MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the > Installer Form." & vbCrLf & _ > "Installer Form was not Saved.", _ > Title:="C.E.S." > > End If > > End Sub -- Dave Peterson
From: Brian on 2 Feb 2010 21:07 Did I do it correctly by moving all my declaration up to the top, instead of having them in each sub? "OssieMac" wrote: > Hi Brian, > > When the code is in the Userform module, the system knows that the controls > belong to the userform by default. when you move it out of the userform > module, you have to tell the syetem where the controls are. > > Looking at your code the following appear to be references to controls on > the userform. > CLLI_Code_1.Value > TEO_No_1.Value > CES_No_1.Value > TEO_Appx_No_2.Value > > You need to prefix them with the userform name and a dot like the following. > > Userform1.CLLI_Code_1.Value > > -- > Regards, > > OssieMac > > > "Brian" wrote: > > > This code worked perfect when it was in the User Form Code Window, but I > > moved it to a module and I get a Run Time Error "424' Object Required > > > > Code in the User Form Window > > -------------------------------------------------- > > '******************************************************* > > 'Save Installer Forms 11 Control Button > > 'Located in M3_Save_Workbook > > '******************************************************* > > > > Private Sub Save_Installer_Forms_11_Click() > > > > Call Save_Installer_Forms > > > > End Sub > > > > > > Code Located in the module: > > > > Declarations > > ---------------------------------------- > > Dim strFile As String > > Dim fileSaveName As Variant > > Dim myMsg As String > > > > > > 'Save Installer Forms 11 Control Button > > Sub Save_Installer_Forms() > > > > ' Dim strFile As String > > ' Dim fileSaveName As Variant > > ' Dim myMsg As String > > > > strFile = "FORMS " & CLLI_Code_1.Value _ "This Block Turns Yellow" > > & Space(1) & TEO_No_1.Value _ > > & Space(1) & CES_No_1.Value _ > > & Space(1) & TEO_Appx_No_2.Value > > > > fileSaveName = Application.GetSaveAsFilename _ > > (InitialFileName:=strFile, _ > > fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") > > > > If fileSaveName <> False Then > > ActiveWorkbook.SaveAs Filename:= _ > > fileSaveName, _ > > FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ > > CreateBackup:=False > > Else > > MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the > > Installer Form." & vbCrLf & _ > > "Installer Form was not Saved.", _ > > Title:="C.E.S." > > > > End If > > > > End Sub
From: Brian on 2 Feb 2010 21:20
What about when I have code like this? Do I need to put the UserForm1 in place of "Me"? This code is suposed to get data from a workbook and load the UserForm1. Sub Load_Job_Data_Spec() With Workbooks("Master Engineering Spec.xlsm").Sheets("Job Data") 'Site Information: Me("CLLI_Code_1").Value = .Range("D02").Value Me("Office_1").Value = .Range("D03").Value Me("Address_11").Value = .Range("D04").Value Me("Address_12").Value = .Range("D05").Value End With End Sub What about this code? This code is suposed to get data from the UserForm1 and load the Workbook. Sub Load_Job_Data_Spec() With Workbooks("Master Engineering Spec.xlsm").Sheets("Job Data") 'Site Information: .Range("B06").Value = Me("Office_1").Value .Range("B08").Value = Me("TEO_No_1").Value .Range("B10").Value = Me("Location_2").Value End With End Sub "OssieMac" wrote: > Hi Brian, > > When the code is in the Userform module, the system knows that the controls > belong to the userform by default. when you move it out of the userform > module, you have to tell the syetem where the controls are. > > Looking at your code the following appear to be references to controls on > the userform. > CLLI_Code_1.Value > TEO_No_1.Value > CES_No_1.Value > TEO_Appx_No_2.Value > > You need to prefix them with the userform name and a dot like the following. > > Userform1.CLLI_Code_1.Value > > -- > Regards, > > OssieMac > > > "Brian" wrote: > > > This code worked perfect when it was in the User Form Code Window, but I > > moved it to a module and I get a Run Time Error "424' Object Required > > > > Code in the User Form Window > > -------------------------------------------------- > > '******************************************************* > > 'Save Installer Forms 11 Control Button > > 'Located in M3_Save_Workbook > > '******************************************************* > > > > Private Sub Save_Installer_Forms_11_Click() > > > > Call Save_Installer_Forms > > > > End Sub > > > > > > Code Located in the module: > > > > Declarations > > ---------------------------------------- > > Dim strFile As String > > Dim fileSaveName As Variant > > Dim myMsg As String > > > > > > 'Save Installer Forms 11 Control Button > > Sub Save_Installer_Forms() > > > > ' Dim strFile As String > > ' Dim fileSaveName As Variant > > ' Dim myMsg As String > > > > strFile = "FORMS " & CLLI_Code_1.Value _ "This Block Turns Yellow" > > & Space(1) & TEO_No_1.Value _ > > & Space(1) & CES_No_1.Value _ > > & Space(1) & TEO_Appx_No_2.Value > > > > fileSaveName = Application.GetSaveAsFilename _ > > (InitialFileName:=strFile, _ > > fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") > > > > If fileSaveName <> False Then > > ActiveWorkbook.SaveAs Filename:= _ > > fileSaveName, _ > > FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ > > CreateBackup:=False > > Else > > MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the > > Installer Form." & vbCrLf & _ > > "Installer Form was not Saved.", _ > > Title:="C.E.S." > > > > End If > > > > End Sub |