LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection
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)
Actually all of that code is unnecessary. In the Userform's code window:
Private Sub UserForm_Initialize()
Private Sub CommandButton1_Click()
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
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.
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:
Application.Wait (Now() + TimeValue("00:00:03"))
This displays the Userform for 3 seconds then hides it.
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!
At last, time to experiment.
Tried a slight variation on the theme (just a different method of timing).
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.
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.
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.
I'm so intrigued that I've sent you an example that works for me.
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.
As always, a true gentleman with VoG™ and many thanks from me for not giving up when I was all ready to.
This thread is now locked and can not be replied to.