Userform

[TiaXL]

me again

I need some help with userforms. I am using a form to allow a user to select a month that will then run a macro to compile some data for reporting puroposes. I want to allow the user to close the form with the cross

I am using this code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  
   Unload userform1
    userform1.hide
    
End Sub

but this seems to run the macro behind it anyway which is what I DONT want it to do. any suggestions?

 

[VoG]

Perhaps

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  
   If CloseMode = vbFormControlMenu Then Unload UserForm1
    
End Sub

 

[Norie]

Hoe is the code supposed to be triggered?

Do you really need code to close the form?

The default behaviour when the X is clicked is to close the form.

 

[TiaXL]

well it seems the default behaviour here is that the code behind it runs anyway. its probably more to do with my set up as the code above doesnt work

Sub monthview()
Dim ComboBox1 As MSForms.ComboBox
Dim sValue As String
Dim sStartdate As Long
Dim sEnddate As Long
Application.ScreenUpdating = False

userform1.Show
sValue = Sheets("Sheet1").Range("B18").Value

If sValue = "January" Then
    sStartdate = 40544
    sEnddate = 40574
End If
If sValue = "February" Then
    sStartdate = 40575
    sEnddate = 40602
End If
If sValue = "March" Then
    sStartdate = 40603
    sEnddate = 40633
End If
If sValue = "April" Then
    sStartdate = 40634
    sEnddate = 40663
End If
If sValue = "May" Then
    sStartdate = 40664
    sEnddate = 40694
End If
If sValue = "June" Then
    sStartdate = 40695
    sEnddate = 40724
End If
If sValue = "July" Then
    sStartdate = 40725
    sEnddate = 40755
End If
If sValue = "August" Then
    sStartdate = 40756
    sEnddate = 40786
End If
If sValue = "September" Then
    sStartdate = 40787
    sEnddate = 40816
End If
If sValue = "October" Then
    sStartdate = 40817
    sEnddate = 40847
End If
If sValue = "November" Then
    sStartdate = 40848
    sEnddate = 40877
End If
If sValue = "December" Then
    sStartdate = 40878
    sEnddate = 40908
End If
Sheets("Sheet1").Select
Range("i3").Value = sStartdate
Range("k3").Value = sEnddate
Sheets("Temp").Select
Range("a2:y65500").Select
Selection.ClearContents
Sheets("Sheet1").Select
Sheets("Data").Select
Range("1:1").Select
Selection.AutoFilter
Selection.AutoFilter field:=22, Criteria1:=">=" & sStartdate, Operator:= _
        xlAnd, Criteria2:="<=" & sEnddate
Range("u1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Temp").Select
Range("a2").Select
Selection.PasteSpecial xlPasteValues
Sheets("Data").Select

Sheets("Data").Select
Range("1:1").Select
Selection.AutoFilter
Sheets("Sheet1").Select
MsgBox ("done")
End Sub

this is my code tat runs in the back although I changed the names of the sheets as they are company specific.

in the actual userform code area I have the following

Option Explicit
 
Private Sub CommandButton1_Click()
Sheets("Drill").Range("b18").Value = ComboBox1.Value
userform1.hide
End Sub
Private Sub UserForm_Activate()
 
End Sub
Private Sub UserForm_Click()
 
 
End Sub
Private Sub UserForm_Initialize()
 

ComboBox1.AddItem "January"
ComboBox1.AddItem "February"
ComboBox1.AddItem "March"
ComboBox1.AddItem "April"
ComboBox1.AddItem "May"
ComboBox1.AddItem "June"
 ComboBox1.AddItem "July"
ComboBox1.AddItem "August"
ComboBox1.AddItem "September"
ComboBox1.AddItem "October"
ComboBox1.AddItem "November"
ComboBox1.AddItem "December"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then Unload userform1
    
End Sub

 

[Norie]

What's triggering the sub monthview?

I don't see anything in the userform code that's calling it.

Do you perhaps have event code for the worksheet(s)?

Or are you running that sub manually or from a button?

If you are doing that, then once the userform is closed execution will return to the sub and
continue with the rest of the code in the sub.

 

[TiaXL]

What's triggering the sub monthview?

I don't see anything in the userform code that's calling it.

Do you perhaps have event code for the worksheet(s)?

Or are you running that sub manually or from a button?

If you are doing that, then once the userform is closed execution will return to the sub and
continue with the rest of the code in the sub.

yes its run from a button I see the logic behind what you are saying, I dont however know how to code for it.

What do I need to do to fix this issue ?

 

[Norie]

You could create another sub that only opens the form.

Then you could call the monthview sub from the form.

Perhaps from the command button you already have on the form?