VBA Code to close userform after data selection only

[mattless1]

I have a list of 10 shops as a list box named lstitems i need the user to click one of the 10 shops and when they click the selected shop it tells then thanks and the name they selected. then i require a Quit button that 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. so they must select or it will not close.

Do you think this can be done?

Many thanks in advance for any help!

 

[HalfAce]

Can it be done? Of course. (If we can figure out exactly what you're looking to do.)
I made a userform and gave it a listbox. Named that lstitems (as in your post).
I gave the userform a button (named "Quit") and set the visible property to False
Then for the listbox code I wrote:

Private Sub lstitems_Click()
MsgBox "Thanks " & lstitems.Value '(I'm really not sure what you want to do here...)
Me.Quit.Visible = True
End Sub

and then for the Quit button code I gave it this:

Private Sub Quit_Click()
Range("D3").Value = Me.lstitems.Value
Unload Me
End Sub

And then a bit of code for the userform itself:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then MsgBox "You can not close the form until you select from the list": Cancel = True
End Sub

In this case, there is no need to test for a selection in the listbox. The Quit button doesn't appear until a selection is made, and the code for the userform disables the little X to close it with, so the user can't bypass your selection that way.

Now, unless there's more to what's being done than what you've described, I'd likely do it a little differently.
I'd have the userform with the listbox (and the userform code to prevent the use of the close X) but I wouldn't need the button if I just put all the action into the listbox code. (ie.)

Private Sub lstitems_Click()
MsgBox "Thanks " & lstitems.Value
Range("D3").Value = Me.lstitems.Value
Unload Me
End Sub

But that's just my preference.

Does any of this help?

 

[mattless1]

Hi, the last code works a treat many thanks for taking the time out to help me with that.

Would it be possible to put a space between the shop name and Click OK to continue in the message MsgBox

Private Sub lstitems_Click()
MsgBox lstitems.Value & "Thanks Click ok to continue"
Range("D3").Value = Me.lstitems.Value
Unload Me
End Sub

 

[mattless1]

I think i have it now i put a page break in unless you have a different way

works thanks again for the first part

 

[HalfAce]

Would it be possible to put a space between the shop name and Click OK to continue in the message MsgBox

Sure, just add a space in the message box text between the first double quote and the 'T' in Thanks.

Another option would be to put them each on separate lines in the message box. (ie.)

MsgBox lstitems.Value & vbNewLine & "Thanks Click ok to continue"

Hope it helps.

 

[mattless1]

Works a treat thanks, is there a way to lock the userform so it can't be moved by the user?

 

[HalfAce]

It's pretty much at that point where I start slappin' my users around to impress upon them the importance of using these things properly.

But... that not being an option, yes, you can hide the userform title bar so there's nothing for them to 'grab' onto and drag.

First (and VERY important) is to make sure you give it a button or something to be able to unload, hide or otherwise dismiss the userform because the little x in the upper right corner is going to be gone.

Once you make sure you have that, put something like this in a standard code module.

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

And then use (or include) this in your userform initialize event.

Private Sub UserForm_Initialize()
HideTitleBar Me
End Sub

(But, if you want to make a bigger impression, I recommend the slappin' method.)

Hope it helps.

 

[mattless1]

Thank you works well.

Many Thanks

 

[HalfAce]

Most welcome. Glad it helped.

 

[mikerickson]

If the user accidently invokes the userform, how do you want them to dismiss it without alerting the data on the worksheet?

(Who would have thought that Half Aces slapping approach was user friendly? )