Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

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

Hi,



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

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")
Theme.SetFocus
Exit Sub
End If


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


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


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


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


If Year.ListIndex = -1 Then
Cancel = 1
MsgBox ("Please Select Year")
Year.SetFocus
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
  #2  
Old 07-13-2017, 07:51 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 404
NoSparks is on a distinguished road
Default

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

Hi,
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.

HTH
Silentwolf
Reply With Quote
  #4  
Old 09-21-2017, 02:35 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 135
Debaser is on a distinguished road
Default

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
Reply

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 03:46 PM.


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