Resizing a form due to it being cut off at bottom of screen

[KaineR]

Hello All,

First of all i have to advise that i'm not too familiar with VBA coding however i need your help.

I work for a managed service provider and one of our clients use an excel spreadsheet to input and send data via an Excel VBAproject form.
The author who created the form has left the company.

The issue is when the users work from other devices other than their work PC's the form is too large, can't be moved and the lower section gets cut off. Therefore they can't click the save button.

Can you please advise how i resize the form?

Code is

Private Sub CommandButton1_Click()


End Sub


Private Sub CheckBox1_Click()


End Sub


Private Sub CheckBox10_Click()


End Sub


Private Sub ComboBox1_Change()


End Sub


Private Sub ComboBox10_Change()


End Sub


Private Sub ComboBox2_Change()


End Sub


Private Sub Label12_Click()


End Sub


Private Sub Label2_Click()


End Sub


Private Sub Label25_Click()


End Sub


Private Sub Label4_Click()


End Sub


Private Sub Label5_Click()


End Sub


Private Sub MultiPage1_Change()


End Sub


Private Sub Send_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)


End Sub


Private Sub Send_Click()








Sheet1.Range("B21") = ListBox1.Value
Sheet1.Range("B23") = ListBox2.Value
Sheet1.Range("B24") = ListBox3.Value
Sheet1.Range("B1") = TextBox1.Value
Sheet1.Range("B2") = TextBox2.Value
Sheet1.Range("B3") = ComboBox1.Value
Sheet1.Range("B5") = ComboBox2.Value
Sheet1.Range("B4") = ComboBox3.Value
Sheet1.Range("B6") = TextBox6.Value
Sheet1.Range("B7") = TextBox7.Value
Sheet1.Range("B8") = TextBox8.Value
Sheet1.Range("B9") = TextBox9.Value
Sheet1.Range("B10") = TextBox10.Value
Sheet1.Range("B11") = TextBox11.Value
Sheet1.Range("B17") = TextBox18.Value
Sheet1.Range("B19") = ComboBox4.Value
Sheet1.Range("B23") = ListBox2.Value
Sheet1.Range("B24") = ListBox3.Value
Sheet1.Range("B25") = TextBox24.Value
Sheet1.Range("B26") = TextBox25.Value
Sheet1.Range("B27") = TextBox26.Value
Sheet1.Range("B28") = TextBox27.Value
Sheet1.Range("B29") = TextBox28.Value
Sheet1.Range("B30") = TextBox29.Value
Sheet1.Range("B31") = TextBox30.Value
Sheet1.Range("B18") = TextBox31.Value
Sheet1.Range("B32") = CheckBox1.Value
Sheet1.Range("B33") = CheckBox2.Value
Sheet1.Range("B34") = CheckBox3.Value
Sheet1.Range("B35") = CheckBox4.Value
Sheet1.Range("B15") = CheckBox5.Value
Sheet1.Range("B37") = CheckBox6.Value
Sheet1.Range("B42") = CheckBox11.Value
Sheet1.Range("B43") = TextBox32.Value
Sheet1.Range("B12") = ComboBox10.Value
Sheet1.Range("B13") = ComboBox7.Value
Sheet1.Range("B14") = ComboBox8.Value
Sheet1.Range("B15") = ComboBox5.Value
Sheet1.Range("B16") = ComboBox9.Value
  


ChDir ActiveWorkbook.Path & "\"
    fileSaveName = Sheet1.Range("H2")
    
     ThisWorkbook.Sheets("QuoteSlip").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Sheet1.Range("H2") _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
    MsgBox "Document created for" & " " & fileSaveName
    
Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    With OutMail
        .to = ""
        .CC = ""
        .BCC = (Sheet1.Range("H3"))
        .Subject = (Sheet1.Range("C1")) & "- Quotation Request"
        .Attachments.Add (ActiveWorkbook.Path & "\" & Sheet1.Range("H2") & ".PDF")
        .HTMLBody = "Hi Team,   I trust all is well. Please find enclosed quotation request for your consideration. Please provide your best terms based on the attached submission."
        .Display
    End With
   
    Set OutMail = Nothing
    Set OutApp = Nothing
    




  
   End Sub






Private Sub TextBox17_Change()


End Sub


Private Sub TextBox20_Change()


End Sub


Private Sub Send_Enter()


End Sub


Private Sub Send_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)


End Sub


Private Sub TextBox10_Change()


End Sub


Private Sub UserForm_Click()


End Sub

I did find this solution but not sure where to paste it and what to modify

[COLOR=#68737D][FONT=Consolas]With UserForm1[/FONT][/COLOR]  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
[COLOR=#68737D][FONT=Consolas]End With[/FONT][/COLOR]

Thanks and i'll await your feedback.

 

[AndyMachin]

Re: Help with resizing a form due to it being cut off at bottom of screen

If only the lower section gets cut off, then you can try making the form show closer to the upper side of the screen. Paste the following code at the end of where you found all those codes:

Private Sub UserForm_Activate()
With UserForm1
    .StartUpPosition = 0
    .Top = Application.Top + 10
End With
End Sub

So to answer the last question, the code you found should go into the UserForm1_Activate event
You don't need the .Left parameter, just the .Top

The number 10 indicates (in pixels) how close or far the upper side of the form will be from the upper side of the screen, Dont go below 2 or above 50
That is just a quick fix, if you want to make the form resizable then more code is needed

 

[My Aswer Is This]

Re: Help with resizing a form due to it being cut off at bottom of screen

Want to get more and more controls on your Userform without making the Userform larger and larger?

Try adding a Multipage to your Userform.

A Multipage can be set to look like 5 or more Userforms all on the same Userform.

If you have never used Multipages look into it and you will see it's a great control.

A multipage is sort of like a Workbook where you can add many Multipage Pages.

A workbook can have many sheet.
A multipage on your userform can have many multipage pages.
A Workbook has Tabs you can click on to go to each sheet.
A Multipage has Tabs you can click on to go to each Multipage page

Check it out.

 

[KaineR]

Thanks Andy, That worked!

I did have to drop the dots in front of the position code though as it threw an error.

Final code is

Private Sub UserForm_Activate()With UserForm1
    StartUpPosition = 0
    Top = Application.Top + 10
End With
End Sub