Run Time Error on trying to save workbook
in [Programming]
|
Prev: RefEdit in UDF ?
Next: Found the answer...
From: OssieMac on 2 Feb 2010 21:55 Hi Brian, What about when I have code like this? Do I need to put the UserForm1 in place of "Me"? Yes. Use the Userform name when referring to the userform's controls except when the code is within the userforms' module. You can only use Me if the code is within the userform's module. Assume you have 2 userforms. Each has its own code module. Code within each module can use Me in lieu of the Userform name because by default it refers to the userform to which the code module belongs. With code outside the userform module the system would not know which userform is being referred to. Note also that each worksheet has it own module and code within that module can use Me to refer to the worksheet to which the module belongs but outside of the worksheet module you have to use the worksheet name. While you can use the parenthesis and enclose the control name in double quotes you should be able to just place a dot between them like this. Userform1.CLLI_Code_1.Value -- Regards, OssieMac "Brian" wrote: > 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
From: OssieMac on 2 Feb 2010 22:06 Hi Brian, Variables simply declared at the top of the module before any other subs but still declared as Dim can all be used in any sub within that same module. If you want to use the variables in other modules then declare them at the top of a standard module but use Public. Examples Dim myVariable 'Can use in any sub in same module Public myPublicVariable 'Can be used in any module -- Regards, OssieMac "Brian" wrote: > 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 22:48 ALU or Goodman, not sure who they are? I am a Power Engineer by trade. I do Large DC Power Sytems for the various phone companies here in the south east. "Dave Peterson" wrote: > 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: OssieMac on 2 Feb 2010 23:27 Hi Brian, The code below finds the screen resolution and then uses a proportional method of setting the form size. Unfortunately there are a few hitches. Zoom only zooms the controls on the form and not the form. Screen resolutions for width and height are not proportional. Due to the above my proportional method is not accurate. Especially using the average for the zoom but for forms that don't fill the screen it is not too bad. You might find it better to use Select Case and have fixed settings for each case. You also might want to re-set the Top and Left parameters that I have commente out. However, you did say that if given a pointer you can usually work it out so lets know how you go and I'll be interested in your results. Video display code from the following link http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/ Option Explicit 'API & Public Const declarations at top of module Declare Function GetSystemMetrics32 Lib "user32" _ Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Sub Show_Userform() Dim vidWidth As Long Dim vidHeight As Long Dim dblMultWdth As Double Dim dblMultHt As Double Dim dblZoom As Double vidWidth = GetSystemMetrics32(SM_CXSCREEN) vidHeight = GetSystemMetrics32(SM_CYSCREEN) '1152 and 864 is initial setup resolution dblMultWdth = vidWidth / 1152 dblMultHt = vidHeight / 864 dblZoom = (dblMultWdth + _ dblMultHt) / 2 'Average With UserForm1 .Zoom = 100 * dblZoom .Width = .Width * dblMultWdth .Height = .Height * dblMultHt '.Top 'For Info. Not used here '.Left 'For info. Not used here .Show End With End Sub -- Regards, OssieMac
From: Brian on 2 Feb 2010 23:44
Where did you put this code? "OssieMac" wrote: > Hi Brian, > > The code below finds the screen resolution and then uses a proportional > method of setting the form size. Unfortunately there are a few hitches. > > Zoom only zooms the controls on the form and not the form. > Screen resolutions for width and height are not proportional. > Due to the above my proportional method is not accurate. Especially using > the average for the zoom but for forms that don't fill the screen it is not > too bad. > > You might find it better to use Select Case and have fixed settings for each > case. > > You also might want to re-set the Top and Left parameters that I have > commente out. > > However, you did say that if given a pointer you can usually work it out so > lets know how you go and I'll be interested in your results. > > Video display code from the following link > http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/ > > Option Explicit > > 'API & Public Const declarations at top of module > Declare Function GetSystemMetrics32 Lib "user32" _ > Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long > > Public Const SM_CXSCREEN = 0 > Public Const SM_CYSCREEN = 1 > > > Sub Show_Userform() > Dim vidWidth As Long > Dim vidHeight As Long > Dim dblMultWdth As Double > Dim dblMultHt As Double > Dim dblZoom As Double > > vidWidth = GetSystemMetrics32(SM_CXSCREEN) > vidHeight = GetSystemMetrics32(SM_CYSCREEN) > > '1152 and 864 is initial setup resolution > dblMultWdth = vidWidth / 1152 > dblMultHt = vidHeight / 864 > > dblZoom = (dblMultWdth + _ > dblMultHt) / 2 'Average > > With UserForm1 > .Zoom = 100 * dblZoom > .Width = .Width * dblMultWdth > .Height = .Height * dblMultHt > '.Top 'For Info. Not used here > '.Left 'For info. Not used here > .Show > End With > > End Sub > > -- > Regards, > > OssieMac > |