Close Code In vba

[mattless1]

Can someone help please??

I have a userform, which has a listbox and a transfer data button to a cell inside my workbook.
I want to have a button to close the userform after an item has been selected and transferred, but it must not close the userform until a item has been transferred to the workbook. this is to stop staff bypassing the userform.

Many thanks

 

[Jeffrey Lopez]

What happens when if you use a frmUserForm1.Hide or Unload Me? after the code executes? Does that not work?

 

[mikerickson]

You have an existing TransferData button, you want code for a Close button. Try

[code]Private Sub butClose_Click()
    Call butTransferData_Click
    Unload Me
End Sub

You may have to write a routine for the QueryClose event to insure that data has been transferred before the corner X is clicked.

Dim dataTransfered as Boolean

Private Sub butTransferData_Click()
    Rem some Code

    dataTransfered = True
End Sub
'...
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Not(dataTransfered) Then
        MsgBox "Transfer data please"
        Cancel = True
    End If
End Sub

 

[mattless1]

Thanks i will try that. i have changed it a bit now i have a list of 10 shops i need the user to click one of the shops and when they click the Quit button it transfers the selected Shop to cell D3 & then closes the userform. but if they do not pick a shop it will ask them to pick one before it closes.

Do you think this can be done?

 

[mikerickson]

I'm guessing that the shops are represented by Option Buttons rather than by CheckBoxes.
In each OptionButton change event, set a global Boolean variable (like dataTransfered above) and then test that variable before closing.

 

[HalfAce]

mattless1,
Have you checked your other thread on this same subject?