Run Time Error on trying to save workbook

[Brian]

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

 

[OssieMac]

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

 

[Dave Peterson]

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]

Did I do it correctly by moving all my declaration up to the top, instead of
having them in each sub?

 

[Brian]

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]

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

 

[OssieMac]

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

 

[Brian]

I took the "Me" out and replaced it with "UserForm1" and it works. As I am
sure you know I am still learning this stuff. Most of the time if someone
points me in the right direction I can figue it out.

One of my beggest problems is the display. It works great on my computer,
but if I run it on my laptop half of the form is not visible. I wish there
was a way for the userForm to resize itself according to the monitor. I tried
putting scoll bars in but it look like crap. I know you can do something with
the API Settings in windows, but I don't know enough to try it.

Do you have any sugestions on how to resize it?

 

[Brian]

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.

 

[OssieMac]

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 lin
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

 

[Brian]

Where did you put this code?

 

[Brian]

The problem is that only the User form changes size and not the entire
window. When I get the User Form to fit the screen, 1/2 of the Window is
completely off the screen where you can not close it without moving it.

 

[OssieMac]

Hi Brian,

Did you change any of the code? If you make any alterations to the code and
it does not work then please post it in case that is where the problem is.

All of the code goes in a standard module.

The API declaration and Public const must be at the top of the standard
module before any subs.

Ensure that the zoom property of the form is set to 100 in the forms
properties (or change the 100 in the code to the zoom setting that you have
in the forms properties.)

All properties set by the code are temporary and only last while the form is
displayed.

You may need to set the forms startup position to zero (manual) like the
following if you set top and left position otherwise it will default to
centre irrespective of top and left settings. My apologies; I should have
included that before.

You should be able to close the form by right clicking in the top bar of the
form and close from the drop down menu if the X is not visible.

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
.StartUpPosition = 0 'Over ride the centre position
.Zoom = 100 * dblZoom
.Width = .Width * dblMultWdth
.Height = .Height * dblMultHt
'.Top = 200 'For Info. Can use if you want
'.Left = 100 'For info. Can use if you want
.Show
End With

End Sub

 

[OssieMac]

Are you using maximize screen because that is how I tested it?

Also lookup StartUpPosition in help. You might need to set it to one of the
other options in the forms properties instead of re-setting it in the code.
(Note that setting StartUpPosition in the code over rides the setting in the
properties dialog box.)

 

[Dave Peterson]

You didn't need to replace the Me keyword in the code I suggested.

But that Save_Installer_Forms_11_Click() procedure did have to be in the
Userform module.