+ Reply to Thread
Results 1 to 4 of 4

User form box too small to use - HELP

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    User form box too small to use - HELP

    when i run the userform control the popup box is too small to actually use the form. code attached and picture. please help!! using Excel 2013

    pics attached are the "box" when i run the code and also pic of the objuectuserform object.JPGsmall userform.JPG


    Private Sub CommandButton1_Click()
        Dim myYear As String
        Dim myMonth As String
        Dim wb As Workbook
        
        CommandButton2.Enabled = False
        If Month(Date) = 1 Then
            myMonth = "Dec"
            myYear = Year(Date) - 1
        Else
            myMonth = Left(MonthName(Month(Date) - 1), 3)
            myYear = Year(Date)
        End If
        
        If Dir(ThisWorkbook.Path & "\" & "Copy of Consolidated" & "\" & myYear, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "Copy of Consolidated" & "\" & myYear
        End If
        If Dir(ThisWorkbook.Path & "\" & "Consolidated" & "\" & myYear, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "Consolidated" & "\" & myYear
        End If
        If Dir(ThisWorkbook.Path & "\" & "Budget\mb" & "\" & CInt(myYear) + 1, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "Budget\mb" & "\" & CInt(myYear) + 1
        End If
        If Dir(ThisWorkbook.Path & "\" & "Budget\ep" & "\" & CInt(myYear) + 1, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "Budget\ep" & "\" & CInt(myYear) + 1
        End If
        If Dir(ThisWorkbook.Path & "\" & "BDC\OutGoing" & "\" & myYear, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "BDC\OutGoing" & "\" & myYear
        End If
        If Dir(ThisWorkbook.Path & "\" & "BDC\OutGoing" & "\" & myYear & "\" & myMonth, vbDirectory) = "" Then
               MkDir ThisWorkbook.Path & "\" & "BDC\OutGoing" & "\" & myYear & "\" & myMonth
        End If
        
        If MsgBox("Have you completed downloading sales data from all the POS for this month and placed the files in requisite directory?", vbYesNo) = vbYes Then
            If Dir(ThisWorkbook.Path & "\Consolidated\" & myYear & "\" & myMonth & ".xlsx", vbDirectory) <> "" Then
                MsgBox "The POS data for " & myMonth & " " & myYear & " has already been consolidated"
            Else
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "epstores.xlsm")
                Run ("epstores.xlsm!mycompany")
                wb.Close False
                MsgBox "The POS data for " & myMonth & " " & myYear & " has already been consolidated"
            End If
            If Dir(ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\*.xlsx", vbDirectory) <> "" Then
                MsgBox "BDC has already been emailed the monthly sales for " & myMonth & " " & myYear
            Else
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "bdc.xlsm")
                'Run ("bdc.xlsm!myBDCmail")
                wb.Close False
                'MsgBox "BDC has already been emailed the monthly sales for " & myMonth & " " & myYear
            End If
        End If
        CommandButton2.Enabled = True
        ThisWorkbook.Close False
    End Sub
    
    Private Sub CommandButton2_Click()
        Dim myYear As String
        Dim myMonth As String
        Dim wb As Workbook
        
        If Month(Date) = 1 Then
            myMonth = "Dec"
            myYear = Year(Date) - 1
        Else
            myMonth = Left(MonthName(Month(Date) - 1), 3)
            myYear = Year(Date)
        End If
        
        CommandButton1.Enabled = False
        If Dir(ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx") <> "" Then
            If MsgBox("Have you entered the data received from franchisee for the month " & myMonth & " " & myYear & "?", vbYesNo) = vbNo Then
                Workbooks.Open (ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx")
                ThisWorkbook.Close False
            Else
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx")
                mySendMail "Sales data for " & myMonth & " " & myYear, "[email protected]", "[email protected];[email protected]", "Hi Rene" & Chr(10) & Chr(10) & "Here is the sales data for " & myMonth & " " & myYear & Chr(10) & Chr(10) & "With regards" & Chr(10) & Chr(10), ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx"
                wb.Close False
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\Consolidated\" & myYear & "\" & myMonth & ".xlsx")
                wb.SaveAs ThisWorkbook.Path & "\copy of Consolidated\" & myYear & "\" & myMonth & ".xlsx"
                wb.Close False
                Kill ThisWorkbook.Path & "\Consolidated\" & myYear & "\" & myMonth & ".xlsx"
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx")
                wb.SaveAs ThisWorkbook.Path & "\Consolidated\" & myYear & "\" & myMonth & ".xlsx"
                wb.Close
                Set wb = Workbooks.Open(ThisWorkbook.Path & "\Consolidated\" & myYear & "\" & myMonth & ".xlsx")
                wb.Sheets(1).Columns("V:V").Cut
                wb.Sheets(1).Columns("B:B").Insert Shift:=xlToRight
                wb.Sheets(1).Columns("k:k").Copy
                wb.Sheets(1).Range("k1").PasteSpecial Paste:=xlPasteValues
                wb.Sheets(1).Columns("c:j").Delete
                wb.Save
                wb.Close
                'Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Consolidated.xlsm")
                'Run ("Consolidated.xlsm!macro1")
                'wb.Close False
            End If
        Else
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "BDCConsolidate.xlsm")
            Run (wb.Name & "!omacro")
            wb.Close False
            MsgBox ("Update the sales sheet by the data received from franchisee and sales portal")
            Workbooks.Open (ThisWorkbook.Path & "\BDC\OutGoing\" & myYear & "\" & myMonth & "\" & myMonth & ".xlsx")
            ThisWorkbook.Close False
        End If
        CommandButton1.Enabled = True
    End Sub
    
    Private Sub UserForm_Initialize()
        Me.Caption = "Welcome to Extreme Brandz Monthly Sales Computing Portal"
        Me.Move 0, 0, Application.Width, Application.Height
    End Sub
    
    Private Sub mySendMail(mySubject As String, myTo As String, myCC As String, myBody As String, myAttachment As String)
    
        Set myapp = CreateObject("outlook.application")
        Set myItem = myapp.createitem(0)
        With myItem
            .Subject = mySubject
            .to = myTo
            .cc = myCC
            .body = myBody & myapp.GetNameSpace("MAPI").CurrentUser
            .attachments.Add myAttachment
            .readreceiptrequested = True
            .send
        End With
        Set myItem = Nothing
        Set myapp = Nothing
    End Sub

  2. #2
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: User form box too small to use - HELP

    These subs need to be in the userform's code module and not a standard module.
    Private Sub CommandButton1_Click()
    Private Sub CommandButton2_Click()
    Private Sub UserForm_Initialize()
    The only code that you have there that controls the size and position of the userform is
    Private Sub UserForm_Initialize()
        Me.Caption = "Welcome to Extreme Brandz Monthly Sales Computing Portal"
        Me.Move 0, 0, Application.Width, Application.Height
    End Sub
    If you put a breakpoint on the line that says
    Private Sub UserForm_Initialize()
    and then step through you'll see that the userform changes size and location once the initialize sub ends. If you have other code that you haven't posted, could that be changing the values again? Stepping through your code should enable you to see which code is running when the userform loads.
    Please click *Add Reputation if I've helped

  3. #3
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: User form box too small to use - HELP

    so interestingly enough - i just opened the file in another laptop and the userform came up and was the entire display - no problems at all. i am guessing it is a problem with my display settings? anyone have an idea as to what to check

  4. #4
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: User form box too small to use - HELP

    Why don't you use VBA to query the values of application.height and application.width and see if that throws anything up. I noticed that when I mocked up a userform and used your initialize code, the form limits at the right and bottom were off the screen, whereas my maximised Excel application was not. Perhaps your original computer can't handle instructions to display something outside the limits of the screen. What happens when the Excel application is not covering the entire screen and you load the userform? Based on your code I would expect that no matter where the Excel window is located on the screen, the userform should display in the top left corner of your screen and be the same size as your Excel window. Is that what happens?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User-Form Problem: User can select headers in User Form (I want Header/no way to select)
    By navialivad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2014, 10:24 AM
  2. [SOLVED] Small help with user form to create a database
    By zaska in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 07-21-2013, 10:47 PM
  3. [SOLVED] User Form to execute search and return all values to the user form for editing
    By allwrighty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2013, 10:40 PM
  4. [SOLVED] Excel vba user form- open directly to user form not worksheet
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 04-24-2013, 05:07 PM
  5. [SOLVED] Excel user form- If/Then statement outcome to show on user form
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2013, 09:16 AM
  6. Help on very small user form
    By ronlaboa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2012, 03:20 AM
  7. Print preview from user form opened from a user form
    By Brunstgnegg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2011, 05:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1