Excel UserForm question

  Chris the Ancient 11:17 27 Jan 2007
Locked

I have a large spreadsheet system, and I'm making one or two minor modifications to it to make it even more user-friendly.

By using UserForms, I can put up little progress messages.

The 'untidy' bit for me is that the UserForm has a 'close' button - regardless. But, in Access, you can program the properties of a form to not have the 'close' button.

Has anyone found a way of doing it in Excel?

Out for a couple of hours (darned work interfering again) and will check the millions of correct answers when I get back ;o)

CtA

  VoG II 11:52 27 Jan 2007

See click here NoCloseButton.zip.

This code is rather old. If like me you are using Excel 2003 you need to change or add

Case 9 'Excel 2000

Case 11 'Excel 2003

  VoG II 14:06 27 Jan 2007

Actually all of that code is unnecessary. In the Userform's code window:

Private Sub UserForm_Initialize()
HideCloseButton Me
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

and in a regular module

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Const WS_SYSMENU = &H80000
Const GWL_STYLE = (-16)

Sub HideCloseButton(oDialog As Object)
Dim hWnd As Long, lStyle As Long
hWnd = FindWindow("ThunderDFrame", oDialog.Caption)
lStyle = GetWindowLong(hWnd, GWL_STYLE)
SetWindowLong hWnd, GWL_STYLE, lStyle And Not WS_SYSMENU
End Sub

Sub ShowUserForm()
frmNoClose.Show
End Sub

  Chris the Ancient 14:19 27 Jan 2007

As always, I never cease to be amazed and how you find these things!

I shall grab my lunch, do one or two domestic things and then play.

Many thanks for your wisdom.

CtA

  VoG II 19:03 27 Jan 2007

Amazingly an almost identical question appeared on MrExcel today. The OP found that Excel seemed to hang when an attempt was made to Hide the Userform. I found that this works:

Sub ShowUserForm()
frmNoClose.Show vbModeless
Application.Wait (Now() + TimeValue("00:00:03"))
frmNoClose.Hide
End Sub

This displays the Userform for 3 seconds then hides it.

  Chris the Ancient 12:16 28 Jan 2007

Due to circumstances beyond my control (I was in a very lazy mood yesterday afternoon), I didn't get as far as trying your idea last night!

So I shall try this new one this afternoon!

CtA

  Chris the Ancient 13:18 30 Jan 2007

At last, time to experiment.

Tried a slight variation on the theme (just a different method of timing).

But...

The close button is still there!

The one I meant (in case I wasn't totally clear - which is quite probable with ne!) is the one in the top title bar of the window.

Anyway, it's not desperate.

CtA

  VoG II 15:36 01 Feb 2007

Before posting I tried the code that I posted above and the Close button (X) does not appear (Excel 2003, Win XP).

I would start with a new workbook, create a Userform with nothing on it then add the code and see if it works. ESC should still close the form.

  Chris the Ancient 18:30 01 Feb 2007

Just got in and picked this up.

Still doesn't do it for me (Excel 2003, Win XP Home).

Created the form with just a text label in it. Copied the code and pasted in a module.

Didn't work. I still had a close button.

Tried a detect and repair and did it all again. I still had a close button.

There must be some vagary in my pc somewhere (although it was reloaded from scratch about a month ago).

So, I shall have to learn without the niceties of non-existent close buttons!

Thanks for trying, though. I appreciate the effort and time - as always.

CtA

  VoG II 19:04 01 Feb 2007

I'm so intrigued that I've sent you an example that works for me.

  Chris the Ancient 19:32 01 Feb 2007

And that one you sent works for me.

For those that have been following the thread, I was missing out the two blocks of code between the 'regular mode' and the final sub routine.

Doh!

As always, a true gentleman with VoG™ and many thanks from me for not giving up when I was all ready to.

CtA

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?