Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

LinkBack Thread Tools Display Modes
Old 07-12-2017, 12:16 AM
Mikedk64 Mikedk64 is offline Windows 10 Office 2010 64bit
Join Date: Jul 2017
Posts: 4
Mikedk64 is on a distinguished road
Default Userform close after use


I don't know if this is possible, but if anyone can answer, must it be you guys

I have this userform, where I have a code that tells you, if you forgot to fill out a box.
I would love to have it so the userform closes after you have pressed the Add button.

If i use a "unload me" in the vba, is there a Rune-time error 2110, because it cant make focus on the boxes you didnt fill out.
So in my eyes do I have the option to have the msgbox telling you miss a box OR that the userform closes after pressing the Add button.

Is there a way to make both work?
This is the code

Private Sub Label1_Click()
If Len(Label1.Caption) <> 0 Then
        Shell "explorer.exe" & " " & Label1.Caption, vbNormalFocus
    End If
    End Sub
Private Sub Add_Click()

If Theme.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Theme")
Exit Sub
End If

If Age.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Age")
Exit Sub
End If

If Gender.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Gender")
Exit Sub
End If

If Resp.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Responsible")
Exit Sub
End If

If Month.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Month")
Exit Sub
End If

If Year.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Year")
Exit Sub
End If

If Label1 = "" Then
Cancel = 1
MsgBox ("Please Select Folder")
Exit Sub
End If

Dim erow As Long
Arow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(Arow, 7).Value = Type1.Value
If Type1.Value <> "" Then
    Cells(Arow, 1).Value = Theme.Value
    Cells(Arow, 2).Value = Val(Age.Text)
    Cells(Arow, 3).Value = Gender.Value
    Cells(Arow, 4).Value = Resp.Text
    Cells(Arow, 5).Value = Month.Value
    Cells(Arow, 6).Value = Year.Value
    Cells(Arow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(Arow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If

Brow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(Brow, 7).Value = Type2.Value
If Type2.Value <> "" Then
    Cells(Brow, 1).Value = Theme.Value
    Cells(Brow, 2).Value = Val(Age.Text)
    Cells(Brow, 3).Value = Gender.Value
    Cells(Brow, 4).Value = Resp.Text
    Cells(Brow, 5).Value = Month.Value
    Cells(Brow, 6).Value = Year.Value
    Cells(Brow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(Brow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If

Crow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(Crow, 7).Value = Type3.Value
If Type3.Value <> "" Then
    Cells(Crow, 1).Value = Theme.Value
    Cells(Crow, 2).Value = Val(Age.Text)
    Cells(Crow, 3).Value = Gender.Value
    Cells(Crow, 4).Value = Resp.Text
    Cells(Crow, 5).Value = Month.Value
    Cells(Crow, 6).Value = Year.Value
    Cells(Crow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(Crow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If

Drow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(Drow, 7).Value = Type4.Value
If Type4.Value <> "" Then
    Cells(Drow, 1).Value = Theme.Value
    Cells(Drow, 2).Value = Val(Age.Text)
    Cells(Drow, 3).Value = Gender.Value
    Cells(Drow, 4).Value = Resp.Text
    Cells(Drow, 5).Value = Month.Value
    Cells(Drow, 6).Value = Year.Value
    Cells(Drow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(Drow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 7).Value = Type5.Value
If Type5.Value <> "" Then
    Cells(erow, 1).Value = Theme.Value
    Cells(erow, 2).Value = Val(Age.Text)
    Cells(erow, 3).Value = Gender.Value
    Cells(erow, 4).Value = Resp.Text
    Cells(erow, 5).Value = Month.Value
    Cells(erow, 6).Value = Year.Value
    Cells(erow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(erow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If

Frow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(Frow, 7).Value = Type6.Value
If Type6.Value <> "" Then
    Cells(Frow, 1).Value = Theme.Value
    Cells(Frow, 2).Value = Val(Age.Text)
    Cells(Frow, 3).Value = Gender.Value
    Cells(Frow, 4).Value = Resp.Text
    Cells(Frow, 5).Value = Month.Value
    Cells(Frow, 6).Value = Year.Value
    Cells(Frow, 8).Value = Label1.Caption
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(Frow + 0, 8), Address:=Label1.Caption, TextToDisplay:=Label1.Caption
End If
End Sub

Private Sub Label9_Click()

End Sub

Private Sub Source_Click()
    Dim FolderPath As Object
    With Label1
        .BackStyle = fmBackStyleTransparent
        .Font.Name = "Courier New"
        .Font.Underline = True
        .Font.Bold = True
        .Font.Size = 10
        .WordWrap = False
        .ForeColor = vbBlue
        .ControlTipText = "Click Link to open folder."
        Set FolderPath = CreateObject("Shell.Application").BrowseForFolder(0, "Select a Folder...", 0, 0)
        If Not TypeName(FolderPath) = "Nothing" Then Label1.Caption = FolderPath.Items.Item.Path
    End With
    Set FolderPath = Nothing
End Sub

Private Sub Clear_Click()
Dim c As Control
    For Each c In Me.Controls
        Select Case TypeName(c)
            Case "TextBox", "ComboBox"
                c.Text = ""
        End Select
    Next c
End Sub

Private Sub UserForm_Initialize()
    Label1.Caption = ""
    Label1.BackStyle = fmBackStyleTransparent
End Sub
Reply With Quote
Old 07-13-2017, 07:51 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 561
NoSparks will become famous soon enoughNoSparks will become famous soon enough

Care to attach a workbook with the user form ?
Reply With Quote
Old 07-23-2017, 12:32 PM
silentwolf silentwolf is offline Windows 7 64bit Office 2010 64bit
Join Date: Dec 2016
Location: Austria
Posts: 15
silentwolf is on a distinguished road

I would recommend to look at wiseowl website there you find tons of lessons regarding validations in userforms.
I am pretty sure you find exactly what you are after there and adapt it to your needs.

Reply With Quote
Old 09-21-2017, 02:35 AM
Debaser Debaser is online now Windows 7 64bit Office 2010 32bit
Competent Performer
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road

If you put Unload Me at the end of the Add button code, you shouldn't have a problem since the other validation codes would exit the routine before that if need be.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform calls other userform, then populate worksheet Lehoi Excel Programming 0 02-03-2016 02:58 PM
Auto_open & Close gacartwright Excel Programming 0 07-29-2015 03:53 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform Simoninparis Word VBA 2 09-21-2014 03:50 AM
Is it possible to take an input from a UserForm in one document to a UserForm in a do BoringDavid Word VBA 5 05-09-2014 09:08 AM
Close Printpreview and load a Userform Francisco Sousa Word VBA 7 07-07-2012 02:16 PM

All times are GMT -7. The time now is 01:28 AM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft