user form X button

[cocopops2001]

i am having a slight problem with my userform. the form allows the user to either select an existing workbook or create a new work book into which a sheet is to be copied.

its probably something really simple but i want the form to run a macro when a button is pushed but when the X button on the form is clicked the 'sheetcopy' macro still runs but i dont want it to. ie it adds sheets to my master workbook instead of a new one, obviously because the new one is not created. i know i need a line to tell it to abort the 'sheetcopy' but not sure what. Im getting on quite well at this but not used to the VBA commands yet.

i did get this button code from a forum and modified it to suit my needs so maybe deleted/added something i shouldnt have. any help would be great, Thanks

also if you think there is a better way to do this then i open to suggestions

Private Sub CommandButton2_Click()
    
    Workbooks.Add
    
    sheetcopy
    
End Sub

Private Sub UserForm_Click()

End Sub
Private Sub CommandButton1_Click()
        
    OpenCalc
    
    sheetcopy
    
End Sub

Private Sub CommonDialog1_AfterUpdate()

End Sub

Private Sub CommonDialog1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub CommonDialog1_Enter()

End Sub

Private Sub CommonDialog1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

 

[GTO]

I do not see anything in the UF code that should do that. Nor does the dialog get dismissed. Maybe show the code that calls the userform?

 

[cocopops2001]

Sub form()
    
    ActiveSheet.Range("a11:n70").Copy
    
    UserForm1.Show
End Sub

pretty simple, it copies the info first then opens the form which asks the user where they want to paste the sheet, ie existing file or new excel book

 

[cocopops2001]

also the opencalc macro

Sub OpenCalc()

Dim fn
fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", _
        Title:="Open File(s)", MultiSelect:=False)
If fn = False Then
MsgBox "Nothing Chosen, Create New Book"
UserForm2.Show
Else
MsgBox "You chose " & fn
Workbooks.Open fn
End If

End Sub

 

[cocopops2001]

and my second button code

Private Sub CommandButton1_Click()

    Workbooks.Add
    
    sheetcopy
    
End Sub

Private Sub CommonDialog1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub CommonDialog1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub CommonDialog1_AfterUpdate()

End Sub

sorry for multiple posts here, im just a noob

 

[GTO]

Presuming the code shown in post#1 is for Userform1:

Option Explicit
    
Private Sub CommandButton2_Click()
    
    Workbooks.Add
    sheetcopy
End Sub
    
Private Sub CommandButton1_Click()
        
    OpenCalc
    sheetcopy
End Sub

...I am sorry, but I am not seeing it. I think you will find it easier to read if you delete all the currently unused procedures (the empty ones).

Also, in Post#5, you have CommandButton1 again, but with different code. Is that in Userform2?

 

[cocopops2001]

yes the code shown in post one is for userform1 it works fine apart from when i click X in top right of box it still copies the sheet to my master workbook which i dont want it to do. im very new to VBA and not certain of the commands.

how do you define the X button? all i want is for the process to be aborted when the X is clicked on either form.

 

[cocopops2001]

fixed this problem, mod can close thread if they wish. i used this code in the userform

Private Sub UserForm_QueryClose _
  (Cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
    If CloseMode = vbFormControlMenu Then
        UserForm1.Show
    End If
End Sub