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
Bookmarks