Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vba code to minimize a window Results

How do I minimize an Outlook window while sending an email from Excel using VBA.
The macro is in Excel. Can you use Sendkeys on multiple times in your code. I tried using SendKeys ("% { } N") To minimize the active window Tring to simulate ( alt + spacebar and N)

Didn't work.
I'm using another instance of Sendkeys to do something else later.

Any ideas?

I need to maximize a program that is in the task bar. I'm using VBA from
within Excel. I already used AppActivate but it only works if the other
application is ALREADY maximized. I also tried using some calls to the
Windows API, but they also would only bring a window to the top if the window
was ALREADY maximized. I coundn't get a window that was minimized to be the
window on top. Well actually, I came close, but the main problem is FINDING
THE HWND for the window that is minimized in the task bar.

If someone could give me at least the code to find the hwnd to the minimized
window, I cound probably figure it out from there as long as there is a call
that will maximize at the same time.

Hope I was clear enough.

Thanks

When NetName is clicked it opens an external program called VNC, for remote desktop viewing.
Then it waits a second to types the password then hits enter.
However the new session of VNC minimizes to the taskbar instead of displaying for the user. NetName.Caption = a computer name on our work network.
So I want to do a showwindow. It finds a window using
lHwnd = FindWindow(vbNullString, NetName.Caption) that is not equal to zero. When it calls the line "ShowWindow lHwnd, 9" it says Runtime Error 49 Bad DLL Calling Convention.

Code:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName
As String) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long)

Private Sub NetName_Click()
Dim lHwnd As Long
    Shell "C:Program FilesRealVNCVNC4vncviewer " & NetName.Caption, vbNormalFocus
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys "pass1~"
    Application.Wait (Now + TimeValue("0:00:02"))
    lHwnd = FindWindow(vbNullString, NetName.Caption)
    If lHwnd  0 Then
        ShowWindow lHwnd, 9
    End If
End Sub
MS Excel 2003 Windows XP

I am sorry but I really am a novice and mostly self taught via the internet. I am generally confused when it comes to using DLLs. If there is anything that is missing at all that someone would find helpful please let me know also first time asking people that really know anything about VB/VBA.

Thank you anyone that took the time to read this. This isn't urgent by any means really a side project at work I am just finding it frustrating more then anything.

Just started to learn COM add-ins with Office XP and Office XP developer.
One strange, but interesting thing I noted is that I can make a VBA userform
with a minimize and maximize button without using any Windows API.

This is what I have:
A very simple .vba project with the following modules:

a normal VBA userform, only holding a label with caption with the following
code:
----------------------------------------------------------------------------
----------------------

Option Explicit

Private Sub UserForm_Activate()
Me.BackColor = RGB(221, 230, 228)
End Sub

A normal code module with the following code:
--------------------------------------------------------

Option Explicit

Public appHostApp As Application
Public gAddInInst As Object

Public Const PROG_ID_START As String = "!"

Public MenuEvents As CMenuEvents

A Class module with the following code:
------------------------------------------------

Option Explicit

Private WithEvents btnMenuItem1 As Office.CommandBarButton

Friend Sub CreateMenuItems()

With appHostApp.CommandBars.ActiveMenuBar.Controls("Too ls").Controls
Set btnMenuItem1 = .Add
With btnMenuItem1
.Caption = "Load Form"
.OnAction = PROG_ID_START & gAddInInst.progID & PROG_ID_END
.BeginGroup = True
End With
End With

End Sub

Friend Sub DeleteMenuItems()
On Error Resume Next
btnMenuItem1.Delete
End Sub

Private Sub btnMenuItem1_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
UserForm1.Show vbModeless
End Sub

A add-in designer module with the following code:
------------------------------------------------------------

Option Explicit

Implements IDTExtensibility2

Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)

End Sub

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)
'Store startup reference
Set appHostApp = Application
Set gAddInInst = AddInInst

Set MenuEvents = New CMenuEvents
MenuEvents.CreateMenuItems

End Sub

Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As
AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)

If RemoveMode = ext_dm_UserClosed Then
MenuEvents.DeleteMenuItems
End If

'remove references to shutdown
Set appHostApp = Nothing

End Sub

That is all.
Now I have no idea why the VBA userform has these extra buttons.
the only line of code I could think of is this one:
.OnAction = PROG_ID_START & gAddInInst.progID & PROG_ID_END

Thanks for any insight in this.

RBS

Nb: Cross-posted to MS.public.Office.developer.com.add_ins

Short Version
==========
I have created a user form within a Microsoft Excel VBA project, and the form can not be minimized. What am I doing wrong?

Full (Long) Version
==============
In the past I have created a series of Macros for my office. I have different versions of the spreadsheet to handle different situations. I decided to create a single version, with a user input screen at the beginning. To do this I created a user form with check boxes, text boxes, etc. I then pull the options off this into the now unified code. The output is multiple spreadsheeets within the excel workbook and each spreadsheet has a graph. So far so good. The issue is I can't look at the spreadsheet without closing the form/input screen. Once closed there is no easy way for the user to reinitiate the form/input screen. I would like to be able to minimize the form/input screen, access the spreadsheets (to make sure they are correct), then return focus to the form/input screen.

I am running Microsoft Excel XP (2002) and Microsoft Windows XP

As always, I appreciate any input

Hi,

I am looking for a VBA Code which will:

1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.

2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.

My task is to show its Path location in a MsgBox (or to show "file not found")

It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.

A 3 section, VBA, code will be more than appreciated.

Michael

Excel XLA Installation - Distribution package. This is a version of my pseudo Excel Installation Package for distribution of your macros!

Design Theory:
Ok, without getting all crazy with the registry and such, package emulates an installation package. Tried to design with noobs in mind. Heavily commented

with decent amount of error checking. This is a hybrid of my thin weight installation package which has been modified and intesified. When opened .xla is actually hidden window.

What is it?
Allows you to package up your Excel XLA into a self contained installation file. Installation process saves file as XLA, then creates toolbar with new button which activates your macro.

How does it Work?
Simple, simply import your forms, and modules, configure the values in Public module.

Features:
-Customizable Button to activate your macro or form
-Customizable word Art Text for installation proccess
-Customizable Background color for installation proccess
-Package has been optimized so that very minimal configuration to deploy your macro.
-Simply configure required public vars, and import your forms and modules.
-Cool interface to give users feel of actual installation.

Important Notes:
-Hidden Help File, simply format->Sheet->Unhide->Help
-Be sure to configure all required vars
-To see how it works, put a break point in it and step through it
-Install package is designed to launch a SINGLE form or macro. Advanced users could probably modify it to do multiples

To see it in action, download file, and hit the install button. Then once you see how it works, modify it with your package. As always, open source, just ask you don't remove my credit. Modify how you like!

To large for post
http://programminglibrary.com/Progra...%20Package.xls

Archived Downloads
http://programminglibrary.com/Progra...downloads.aspx
Happy Coding............

I came back to a common problem of clearing the office clipboard from Excel VBA (I have WXP, Office 2003 all updates and service packe) to see if there are any elegant solutions posted on this message board, and found none. In fact, I found that many people suggesting help to clearing the clipboard gave help on clearing the windows clipboard rather than the Office clipboard. I did not even see a solution to this issue (at least something that I rcoginized as a solution, forgive me if I missed one), elegant or otherwise, so I am posting the solution I have so people can use it as they see fit, and to see if anyone has improvement suggestions or other methods of doing it.

I especially would like to know what code I could add to automatically open the clipboard, since the code only seems to work if the clipboard is open.
Of course, if someone has 10 lines of code that will empty the clipboard, I would love to see this rather than using this big module.

I am far from an expert on the solution I have, since, after litterally days of googling I found this solution concept on Guoqiang Wu's Blog. According to him, the "handle" (or whatever the technical term is) to access the office clipboard can change as you open and close Excel (or any office program). So you have to search for the handle first, and then send the appropriate signal to the clipboard. Mr. Wu was kind enough to help me modify his implementation so that the search function results gets cached, and any subsequent calls to the clear clipboard function bypasses the search and just clears the clipboard. I am very much indebted to him since clearing the clipboard was a do or die for my project.

Here is my implementation.
to clear the clipboard from within vba just put the code line
ClearOfficeClipboard.

You should run it at least once during any initialization phase of your code so that the overhead for finding the clipboard "handle" does not slow you up latter when you need fast execution.

Also, this is real pain in the whatever,but for this code to work, the clipboard must be visible!!!! I have not found a work around this. And I can't even find a way to make the clipboard visible from within vba, so I have to do it manually! I just minimize the window size and after months of use I do not even notice it there, but talk about brute force! However, since my project will not execute quickly enough without emptying the clipboard frequently of all the garbage it collects that sucks up memory and really slows things down.

Clear the Clipboard Module: ( I just commented out things when I changed them from Mr. Wu's original version that is more generic since it was supposed to work from any office application).

Option Explicit

Const CHILDID_SELF = 0&
Const ROLE_TITLEBAR = &H1&
Const ROLE_MENUBAR = &H2&
Const ROLE_SCROLLBAR = &H3&
Const ROLE_GRIP = &H4&
Const ROLE_SOUND = &H5&
Const ROLE_CURSOR = &H6&
Const ROLE_CARET = &H7&
Const ROLE_ALERT = &H8&
Const ROLE_WINDOW = &H9&
Const ROLE_CLIENT = &HA&
Const ROLE_MENUPOPUP = &HB&
Const ROLE_MENUITEM = &HC&
Const ROLE_TOOLTIP = &HD&
Const ROLE_APPLICATION = &HE&
Const ROLE_DOCUMENT = &HF&
Const ROLE_PANE = &H10&
Const ROLE_CHART = &H11&
Const ROLE_DIALOG = &H12&
Const ROLE_BORDER = &H13&
Const ROLE_GROUPING = &H14&
Const ROLE_SEPARATOR = &H15&
Const ROLE_TOOLBAR = &H16&
Const ROLE_STATUSBAR = &H17&
Const ROLE_TABLE = &H18&
Const ROLE_COLUMNHEADER = &H19&
Const ROLE_ROWHEADER = &H1A&
Const ROLE_COLUMN = &H1B&
Const ROLE_ROW = &H1C&
Const ROLE_CELL = &H1D&
Const ROLE_LINK = &H1E&
Const ROLE_HELPBALLOON = &H1F&
Const ROLE_CHARACTER = &H20&
Const ROLE_LIST = &H21&
Const ROLE_LISTITEM = &H22&
Const ROLE_OUTLINE = &H23&
Const ROLE_OUTLINEITEM = &H24&
Const ROLE_PAGETAB = &H25&
Const ROLE_PROPERTYPAGE = &H26&
Const ROLE_INDICATOR = &H27&
Const ROLE_GRAPHIC = &H28&
Const ROLE_STATICTEXT = &H29&
Const ROLE_TEXT = &H2A&
Const ROLE_PUSHBUTTON = &H2B&
Const ROLE_CHECKBUTTON = &H2C&
Const ROLE_RADIOBUTTON = &H2D&
Const ROLE_COMBOBOX = &H2E&
Const ROLE_DROPLIST = &H2F&
Const ROLE_PROGRESSBAR = &H30&
Const ROLE_DIAL = &H31&
Const ROLE_HOTKEYFIELD = &H32&
Const ROLE_SLIDER = &H33&
Const ROLE_SPINBUTTON = &H34&
Const ROLE_DIAGRAM = &H35&
Const ROLE_ANIMATION = &H36&
Const ROLE_EQUATION = &H37&
Const ROLE_BUTTONDROPDOWN = &H38&
Const ROLE_BUTTONMENU = &H39&
Const ROLE_BUTTONDROPDOWNGRID = &H3A&
Const ROLE_WHITESPACE = &H3B&
Const ROLE_PAGETABLIST = &H3C&
Const ROLE_CLOCK = &H3D&

Type tGUID
****lData1 As Long
****nData2 As Integer
****nData3 As Integer
****abytData4(0 To 7) As Byte
End Type

Type AccObject
****objIA As IAccessible
****lngChild As Long
End Type

'Global objButton As AccObject
'Dim accButton As AccObject

Const WM_GETTEXT = &HD

Public lngChild As Long, strClass As String, strCaption As String

Declare Function AccessibleObjectFromWindow Lib "oleacc" _
********************************************(ByVal hWnd As Long, ByVal dwId As Long, _
******************************************** riid As tGUID, ppvObject As Object) As Long

Declare Function AccessibleChildren Lib "oleacc" _
************************************(ByVal paccContainer As IAccessible, ByVal iChildStart As Long, _
************************************ ByVal cChildren As Long, rgvarChildren As Variant, _
************************************ pcObtained As Long) As Long

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
****************************ByVal lpClassName As String, _
****************************ByVal lpWindowName As String) As Long

Declare Function GetParent Lib "user32" (ByVal hWnd As Long) As Long

Declare Function EnumChildWindows Lib "user32" (ByVal hwndParent _
************************************************As Long, ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, _
******************************************************************ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, _
****************************************************************ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long

Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, _
******************************************************************ByVal hWnd2 As Long, ByVal lpClass As String, ByVal lpCaption As String) As Long

'Retrieve window class name
Function GetWndClass(ByVal hWnd As Long) As String
****Dim buf As String, retval As Long
****buf = Space(256)
****retval = GetClassName(hWnd, buf, 255)
****GetWndClass = Left(buf, retval)
End Function

'Retrieve window title

Function GetWndText(ByVal hWnd As Long) As String
****Dim buf As String, retval As Long
****buf = Space(256)
****retval = SendMessage(hWnd, WM_GETTEXT, 255, buf)
****GetWndText = Left(buf, InStr(1, buf, Chr(0)) - 1)
End Function

'The call back function used by EnumChildWindows
Function EnumChildWndProc(ByVal hChild As Long, ByVal lParam As Long) As Long
****Dim found As Boolean
****EnumChildWndProc = -1
****If strClass > "" And strCaption > "" Then
********found = StrComp(GetWndClass(hChild), strClass, vbTextCompare) = 0 And _
****************StrComp(GetWndText(hChild), strCaption, vbTextCompare) = 0
****ElseIf strClass > "" Then
********found = (StrComp(GetWndClass(hChild), strClass, vbTextCompare) = 0)
****ElseIf strCaption > "" Then
********found = (StrComp(GetWndText(hChild), strCaption, vbTextCompare) = 0)
****Else
********found = True
****End If

****If found Then
********lngChild = hChild
********EnumChildWndProc = 0
****Else
********EnumChildWndProc = -1
****End If
End Function

'Find the window handle of a child window based on its class and titie
Function FindChildWindow(ByVal hParent As Long, Optional cls As String = "", Optional title As String = "") As Long
****lngChild = 0
****strClass = cls
****strCaption = title
****EnumChildWindows hParent, AddressOf EnumChildWndProc, 0
****FindChildWindow = lngChild
End Function

'Retrieve the IAccessible interface from a window handle
'Reference:Jean Ross,Chapter 17: Accessibility in Visual Basic,Advanced Microsoft Visual Basic 6.0, 2nd Edition
Function IAccessibleFromHwnd(hWnd As Long) As IAccessible
****Dim oIA As IAccessible
****Dim tg As tGUID
****Dim lReturn As Long

****' Define the GUID for the IAccessible object
****' {618736E0-3C3D-11CF-810C-00AA00389B71}

****With tg
********.lData1 = &H618736E0
********.nData2 = &H3C3D
********.nData3 = &H11CF
********.abytData4(0) = &H81
********.abytData4(1) = &HC
********.abytData4(2) = &H0
********.abytData4(3) = &HAA
********.abytData4(4) = &H0
********.abytData4(5) = &H38
********.abytData4(6) = &H9B
********.abytData4(7) = &H71
****End With
****' Retrieve the IAccessible object for the form
****lReturn = AccessibleObjectFromWindow(hWnd, 0, tg, oIA)
****Set IAccessibleFromHwnd = oIA
End Function

'Recursively looking for a child with specified accName and accRole in the accessibility tree
Function FindAccessibleChild(oParent As IAccessible, strName As String, lngRole As Long) As AccObject
****Dim lHowMany As Long
****Dim avKids() As Variant
****Dim lGotHowMany As Long, i As Integer
****Dim oChild As IAccessible
****FindAccessibleChild.lngChild = CHILDID_SELF
****If oParent.accChildCount = 0 Then
********Set FindAccessibleChild.objIA = Nothing
********Exit Function
****End If
****lHowMany = oParent.accChildCount
****ReDim avKids(lHowMany - 1) As Variant
****lGotHowMany = 0
****If AccessibleChildren(oParent, 0, lHowMany, avKids(0), lGotHowMany) 0 Then
********MsgBox "Error retrieving accessible children!"
********Set FindAccessibleChild.objIA = Nothing
********Exit Function
****End If

****'To do: the approach described in http://msdn.microsoft.com/msdnmag/issues/0400/aaccess/default.aspx
****' are probably better and more reliable
****On Error Resume Next
****For i = 0 To lGotHowMany - 1
********If IsObject(avKids(i)) Then
************If StrComp(avKids(i).accName, strName) = 0 And avKids(i).accRole = lngRole Then
****************Set FindAccessibleChild.objIA = avKids(i)
****************Exit For
************Else
****************Set oChild = avKids(i)
****************FindAccessibleChild = FindAccessibleChild(oChild, strName, lngRole)
****************If Not FindAccessibleChild.objIA Is Nothing Then
********************Exit For
****************End If
************End If
********Else
************If StrComp(oParent.accName(avKids(i)), strName) = 0 And oParent.accRole(avKids(i)) = lngRole Then
****************Set FindAccessibleChild.objIA = oParent
****************FindAccessibleChild.lngChild = avKids(i)
****************Exit For
************End If
********End If
****Next i
End Function

Function FindAccessibleChildInWindow(hwndParent As Long, strName As String, lngRole As Long) As AccObject
****Dim oParent As IAccessible
****Set oParent = IAccessibleFromHwnd(hwndParent)
****If oParent Is Nothing Then
********Set FindAccessibleChildInWindow.objIA = Nothing
****Else
********FindAccessibleChildInWindow = FindAccessibleChild(oParent, strName, lngRole)
****End If
End Function

'Generic routine to retrieve the window handle of the active window of an Office Application
Function GetOfficeAppHwnd(app As Object) As Long
****GetOfficeAppHwnd = FindWindow(vbNullString, GetOfficeAppWindowTitle(app))
End Function

'Retrieve the window handle of the task pane
'Notice: the task pane window title is localized!
'You can find out the window class and title using Spy, Inspect32 or other tools
Function GetOfficeTaskPaneHwnd(app As Object) As Long
****GetOfficeTaskPaneHwnd = FindChildWindow(GetOfficeAppHwnd(app), _
********************************************"MsoCommandBar", "Task Pane")
End Function

'Retrieve the window handle of the clipboard child window inside task pane
'The window title of the clipboard window seems to be language independent,
'making it a better start point to searching our UI element than the task pane window
Function GetOfficeClipboardHwnd(app As Object) As Long
****GetOfficeClipboardHwnd = FindChildWindow(GetOfficeAppHwnd(app), , "Collect and Paste 2.0")
End Function

'Generic routine to retrieve the window title of the active window of an Office application
Function GetOfficeAppWindowTitle(app As Object) As String
****On Error GoTo ErrorHandler
****Select Case app.Name
****Case "Microsoft Word"
********GetOfficeAppWindowTitle = app.ActiveWindow.Caption & " - " & app.Name
****Case Else
********GetOfficeAppWindowTitle = app.Name & " - " & app.ActiveWindow.Caption
****End Select
****Exit Function

ErrorHandler:
****MsgBox "Unsupported Office application!"
****GetOfficeAppWindowTitle = ""
End Function

'Using Active Accessibility to clear Office clipboard
'Assumption:
'****this is running within Word or Excel as a macro, thus the global Application object is available
Sub ClearOfficeClipboard()
****Static accButton As AccObject
****If accButton.objIA Is Nothing Then
********Dim fShown As Boolean
********fShown = CommandBars("Task Pane").Visible
********wsRAW_DATA.Select
********Range("A1").Copy
********bSuccess = oAIX.Sleep(2000)
********If Not (fShown) Then
************CommandBars("Task Pane").Enabled = True
************CommandBars("Task Pane").Visible = True
************'************************MsgBox ("Press OK and Open Clipboard , then F5 to continue or you will get an error message")
********End If
********'************ CommandBars("Task Pane").Visible = True
********'********** WordBasic.EditOfficeClipboard**'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
********accButton = FindAccessibleChildInWindow(GetOfficeClipboardHwnd(Application), "Clear All", ROLE_PUSHBUTTON)
********'****************CommandBars("Task Pane").Visible = fShown
********'******************CommandBars("Task Pane").Visible = False
****End If
****If accButton.objIA Is Nothing Then
********MsgBox "Unable to locate the ""Clear All"" button!"
****Else
********accButton.objIA.accDoDefaultAction accButton.lngChild
****End If
End Sub

''Using Active Accessibility to clear Office clipboard
''Input: app - the application object of an Office application
''Assumption: Clipboard task pane is shown in the Office application (app object)
'Function ClearOfficeClipboard(app As Object) As Boolean
'********Dim oButton As AccObject, fShow As Boolean
''********Dim fShow As Boolean******'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'********'Get the IAccessible interface and child id (wrapped in the AccObject type)
'********'Notice: the second parameter, accName "Clear All" is localized!
'********'You can find out the accName and accRole using Spy, Inspect32 or other tools
'********oButton = FindAccessibleChildInWindow(GetOfficeClipboardHwnd(app), "Clear All", ROLE_PUSHBUTTON)
'********If oButton.objIA Is Nothing Then
'****************MsgBox "Unable to locate the ""Clear All"" button!"
'****************ClearOfficeClipboard = False
'********Else
'****************oButton.objIA.accDoDefaultAction oButton.lngChild
'****************ClearOfficeClipboard = True
'********End If
'End Function

[/b]

I have a worksheet open to a userform first. I can click on many command buttons all which act as a hyperlink to other ecel spereadsheets. I have code in place that allows me to minimize the userform but I cannot edit the newly opened sheet until I close the userform. How can I accomplish minimizing the userform and edit the newly opened excel sheet?

Here is the code I found here from Leith Ross, for minimizing the user form which does work:
'Written: October 07, 2007
'Author: Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000

Private Declare Function GetWindowLong _
  Lib "User32.dll" _
   Alias "GetWindowLongA" _
    (ByVal hWnd As Long, ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "User32.dll" _
   Alias "SetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) As Long
     
'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
  Lib "User32.dll" _
   (ByVal hWnd As Long) As Long

'Returns the Window Handle of the Window accepting input
 Private Declare Function GetForegroundWindow _
  Lib "User32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

 Dim BitMask As Long
 Dim Window_Handle As Long
 Dim WindowStyle As Long
 Dim Ret As Long

   If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
      Window_Handle = GetForegroundWindow()
  
       WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
       BitMask = WindowStyle Or Box_Type
  
      Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
      Ret = DrawMenuBar(Window_Handle)
   End If

End Sub


Hi There,

I was able to find code (Win API code) to apply to a VBA userform to give it
minimize and maximize buttons.
It also minimizes to the Windows OS taskbar.

I would like to minimize it and then continue to work on the Excel sheet.
But I can't, because the form has the focus even when it is minimized!

So this is how it works : there is a button on a sheet that opens the userForm and then
it is used to do sheet browsing. Then I minimize, and do data entry in the sheet.

I hope there is an easy solution to this.

All help is appreciated.

Sincerely,

Paluee

I've seen pieces of answers to pieces of this question but I've never seen a
complete answer that works. I can get close but there is always a problem
at the end. I thought I had done all of this kind of stuff myself over the
years and when a friend told me he couldn't get it working I just sent him
off examples of code that where the pieces I'd used. But in the end I'd
never used them exactly as stated below so my "do this and do that" memory
of what effects what just fell on its face.

Here's the complete flow needed.

Using the windows task scheduler:

A bat file is run

Excel is called and the "Auto_Open" macro is run.

Various VBA code does its thing, which does not include updating anything in
the workbook or does not include any kinds of prompt, dialog box. The VBA
code just reads and writes texts files and does nothing that would normally
require that an object or variable be set to NOTHING. The proper opens and
closes are done for the file numbers. Very vanilla code.

The workbook needs to close and that seems to happen.

Excel needs to quit, close, go away, gone-already, but it doesn't.

This is where I've seen dozens of "try this" suggestions but in the end
Excel doesn't quit, it just sits there with its grey panel with no workbook
open.

Ideally this whole process should run minimized as well.

Thanks for considering the challenge.

Hi all!

Newbie here and love learning new vba tricks and grasping new concepts.

I have a problem and it's the one thing that is preventing me from crossing the finish line. Have coworkers who are not pc savy and I am trying to minimize the amount of un needed data that they will have to send over their wireless mobile network cards. They work with customers on remote sites and we use excel to process all of our paperwork/forms. I know this is not the best solution, but I am making the best of a mediocre situation.

I have my personal.xls workbook with all the required macros and scripting to create a custom toolbar for them (idiot proof..i mean user friendly) to handle things that I do not want to have to repeat over and over. The personal .xls also has worksheets preformated and coded to mesh well with the workbooks that they are currently working with. I need to be able to copy sheets from the personal.xls workbook to a unknown named workbook (the workbook exists, but the filenames are never going to be the same and I want these to work with older versions of our paperwork). My code is as follows, but I need where it references "2012 Workbooks.xls" to be the current workbook. The code works perfectly with my workbooks named Personal.xls and 2012 Workbooks.xls. Just when I throw the variable workbook name it will fail...of course.
Sub AddWarranty()

' Add Warranty Check List Macro
Application.ScreenUpdating = False

Windows("PERSONAL.XLS").Visible = True
Sheets("Warranty Check List").Select
Sheets("Warranty Check List").Copy After:=Workbooks("2012 Workbook.xls").Sheets("Repair
Order")

'Cleans up lost references
Cells.Replace What:="[PERSONAL.XLS]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Windows("PERSONAL.XLS").Visible = False
Application.ScreenUpdating = True
End Sub
So in the end, they can add forms as needed therefore only sending in necessary forms.
Thank you in advance. I am sure the solution is easy, it is just escaping me at the moment.

I am using Excel 2000 and am having a problem with my custom toolbars disappearing. I used the code Dave posted in the FAQ (http://www.ozgrid.com/VBA/toolbar-remove-restore.htm) to make my toolbars visible and hide Excel's. When I minimize Excel, either using VBA or by right clicking the program tab in Windows, the program minimizes. However, when I restore Excel, my toolbars are gone and the Name Box, Formula Bar and Window Header bar (the bar at the very top that says Microsoft Excel-[file name here] and the main Minimize, Maximize and Close buttons are located) are the only "toolbars" visible.

Below is the code that I use to remove all possible toolbars and then put mine in and format the sheets.


	VB:
	
 Format() 
    Dim objCommandBar As CommandBar 
    On Error Resume Next 
    For Each objCommandBar In Application.CommandBars 
        objCommandBar.Visible = False 
    Next objCommandBar 
    Call RemoveToolbars 
    [deleted code that limits scroll area] 
    On Error Resume Next 
    On Err < 0 Goto Continue 
Continue: 
    Dim FinishCell As Range 
    Set FinishCell = ActiveCell 
     
    x = ActiveSheet.Name 
    Sheets.Select 
    With ActiveWindow 
        .DisplayGridlines = False 
        .DisplayHeadings = False 
        .DisplayHorizontalScrollBar = True 
        .DisplayVerticalScrollBar = True 
        .DisplayWorkbookTabs = False 
    End With 
    Application.DisplayStatusBar = False 
    Application.EnableAutoComplete = False 
    Worksheets(x).Select 
    FinishCell.Select 
    ActiveWorkbook.Protect Structure:=True, Windows:=False 
    Application.ScreenUpdating = True 
End Sub 
 
Sub RemoveToolbars() 
    On Error Resume Next 
    With Application 
        .DisplayFullScreen = True 
        .CommandBars("Full Screen").Visible = False 
        .CommandBars("Repair Log").Enabled = True 
         '.Visible is needed to operate correctly for other macros
        .CommandBars("Repair Log").Visible = True 
        .CommandBars("Repair Log 2").Enabled = True 
        .CommandBars("Repair Log 2").Visible = True 
        .CommandBars("Repair Log 3").Enabled = True 
        .CommandBars("Repair Log 3").Visible = True 
        .CommandBars("Worksheet Menu Bar").Enabled = False 
    End With 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have tested this code in a brand new workbook and I get the same results. Any help would be appreciated. Thank you.

James Buggie

In this thread http://ozgrid.com/forum/viewthread.php?tid=1783 AJW references the VBS for copying files.

Thanks AJW, you have prompted me to TRY and finish one of my Site pages on this DLL....

What AJW has really found is the power of the Windows Core DLLs,
specifically the Shell32.Dll of which one of it's primary responsibilities is
managing and providing access to the wide variety of objects that make up the system
eg. Network printers, Other networked computers, Control Panel applications, The Recycle Bin etc.

The Shell object represents the objects in the Windows Shell.
You can use the methods exposed by the Shell object to:
Open, explore, and browse for folders.(See below)
Minimize, restore, cascade, or tile open windows.
Launch Control Panel applications.
Display system dialog boxes (The copy routine)

This DLL is referenced in AJW's code via this statement;

Set objShell = CreateObject("Shell.Application")

The code doesn't need to be executed via VBS, VBA or any Scripting language that
supports COM will do the job. eg Java
Also the Native VBA code will do it.

Most of the programming elements in the Shell and common controls are contained in three DLLs;
Comctl32.dll
Shell32.dll
and Shlwapi.dll. (Shell light weight API)

You should be mindful of the Shell version because of ongoing enhancements, different versions
of these DLLs implement different features.

Version DLL Distribution Platform
4.0 All Microsoft® Windows® 95/Microsoft Windows NT® 4.0.
4.7 All Microsoft Internet Explorer 3.x.
4.71 All Internet Explorer 4.0.
4.72 All Internet Explorer 4.01 and Windows 98.
5.0 Shlwapi.dll Internet Explorer 5.
6.0 Shlwapi.dll Internet Explorer 6 and Windows XP.
5.0 Shell32.dll Windows 2000 and Windows Millennium Edition (Windows Me).
6.0 Shell32.dll Windows XP.
5.8 Comctl32.dll Internet Explorer 5.
5.81 Comctl32.dll Windows 2000 and Windows Me.
6.0 Comctl32.dll Windows XP.

I am currently doing a write up on these powerful core DLLs of which the Shell32.Dll is one.

[One Example here for Now ]

http://www.xcelfiles.com/Shell32_01.html

Here is just a few examples of what you CAN DO, I'll leave the detailed explaination @ my site when finished.


	VB:
	
 
 
 '// Requires DLL version shell32.dll version 4.71 or later
 '// Operating systems
 '// Win2000, WinNT 4.0 with Internet Explorer*4.0, Win98, Win95 with Internet Explorer*4.0
 
 'Item Identifiers and Identifier Lists
 'The Shell uses object identifiers within the Shell's namespace.
 'All objects visible in the Shell (files, directories, servers, workgroups, and so on)
 'have unique identifiers among the objects within their parent folder.
 'These identifiers are called item identifiers, and they have the SHITEMID data type
 'as defined in the Shtypes.h header file. An item identifier is a variable-length byte
 'stream that contains information that identifies an object within a folder.
 'Only the creator of an item identifier knows the content and format of the identifier.
 'The only part of an item identifier that the Shell uses is the first two bytes,
 'which specify the size of the identifier.
 
 'Each parent folder has its own item identifier that identifies it within its own parent folder.
 'Thus, any Shell object can be uniquely identified by a list of item identifiers.
 'A parent folder keeps a list of identifiers for the items it contains.
 'The list has the ITEMIDLIST data type. Item identifier lists are allocated by the Shell
 'and may be passed across Shell interfaces, such as IShellFolder. It is important to remember
 'that each identifier in an item identifier list is only meaningful within the context of its parent folder.
 
 'An application can set a shortcut's item identifier list by using the SetIDList method.
 'This method is useful when setting a shortcut to an object that is not a file, such as
 'a printer or disk drive. An application can retrieve a shortcut's item identifier list
 'by using the GetIDList method.
 
 'What is a PIDL? Why not just use file system paths?
 'A PIDL (Program ID list) is a way of identifying any namespace object.
 'You can also use paths to identify namespace objects, but only if they are part of
 'the file system. With namespace objects that are not part of the file system, you must use PIDLs.
 
Private Const BIF_BROWSEFORCOMPUTER = &H1000 
Private Const BIF_BROWSEFORPRINTER = &H2000 
Private Const BIF_BROWSEINCLUDEFILES = &H4000 
Private Const BIF_BROWSEINCLUDEURLS = &H80 
Private Const BIF_DONTGOBELOWDOMAIN = &H2 
Private Const BIF_EDITBOX = &H10 
Private Const BIF_NEWDIALOGSTYLE = &H40 
Private Const BIF_RETURNFSANCESTORS = &H8 
Private Const BIF_RETURNONLYFSDIRS = &H1 
Private Const BIF_SHAREABLE = &H8000 
Private Const BIF_STATUSTEXT = &H4 
Private Const BIF_USENEWUI = &H40 
Private Const BIF_VALIDATE = &H20 
 
 
Sub BrowseForFolderShell() 
     '//Minimum DLL version shell32.dll version 4.71 or later
     '//Minimum operating systems   Windows*2000, Windows NT 4.0 with Internet Explorer*4.0,
     '//Windows*98, Windows 95 with Internet Explorer*4.0
    Dim objShell As Object 
    Dim objFolder As Object 
    Dim strFolderFullPath As String 
     
    Set objShell = CreateObject("Shell.Application") 
     'oFolder = Shell.BrowseForFolder(Hwnd, sTitle, iOptions [, vRootFolder])
    Set objFolder = objShell.BrowseForFolder(0, "Please select a folder", BIF_NEWDIALOGSTYLE Or BIF_USENEWUI,
SpecFolders.CSIDL_PERSONAL) 'SpecFolders.CSIDL_FAVORITES
     
    If (Not objFolder Is Nothing) Then 
         '// NB: If SpecFolder= 0 = Desktop then ....
        On Error Resume Next 
        If IsError(objFolder.Items.Item.path) Then strFolderFullPath = CStr(objFolder): Goto GotIt 
        On Error Goto 0 
         '// Is it the Root Dir?...if so change
        If Len(objFolder.Items.Item.path) > 3 Then 
            strFolderFullPath = objFolder.Items.Item.path & Application.PathSeparator 
        Else 
            strFolderFullPath = objFolder.Items.Item.path 
        End If 
    Else 
        MsgBox "User cancelled": Goto Xit 
    End If 
     
GotIt: 
    MsgBox "You selected:= " & strFolderFullPath, vbInformation, "ObjectFolder:= " & objFolder 
     
Xit: 
    Set objFolder = Nothing 
    Set objShell = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

I'm trying to use the Excel-Solver in VBA. After trying a lot of time I didn't found the solution of my problem.

I have a Target cell (C6) and I want to minimize this cell by changing the cells (C3:C5). After using the Solver-function I got most of the time only zero's.
But if I tried it manually, and change for example cell C3 from 0 to 0.1 I got a lower solution then before.

My problem is that the Solver will not produce the right minimum value.

Who can help me with this problem?

Thanx

P.S: This is the VBA-code:

Sub Forecast_Winter()
'
' Forecast_Winter Macro
'
Windows("Worksheet Test.xls").Activate
Sheets("Winter's Method").Select
Range("C3:C5").Select
Selection.ClearContents
SolverReset
SolverOptions precision:=0.001
SolverOk SetCell:="$C$6", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$3:$C$5"
SolverAdd CellRef:="$C$3", Relation:=1, FormulaText:="$J$4"
SolverAdd CellRef:="$C$4", Relation:=1, FormulaText:="$J$4"
SolverAdd CellRef:="$C$5", Relation:=1, FormulaText:="$J$4"
SolverAdd CellRef:="$C$3", Relation:=3, FormulaText:="$J$3"
SolverAdd CellRef:="$C$4", Relation:=3, FormulaText:="$J$3"
SolverAdd CellRef:="$C$5", Relation:=3, FormulaText:="$J$3"

SolverSolve (True)

End Sub

I have inserted several command buttons on an Excel 2003 worksheet. Each button executes a separate VBA macro. I would like one of these macros to make one of the command buttons invisible. The target button (called "Update") will become visible again if the user modifies any data in the worksheet.

Everything works fine, except that the target button remains visible - although inactive - after the code is executed. If I minimize the Excel application window and then maximize it again, the Update button vanishes, as it should. This suggests that the screen must be refreshed after the macro is executed in order to make the button invisible to the user.

I have seen other posts related to this problem (http://www.excelforum.com/excel-prog...ml#post2188184), but I'm having difficulty implementing the solution myself.

If I add the following line to my macro:

I get the following error box:  
	
		
			
			
				Compiling error. Could not find the method or data member.
			
		
	
It seems that the Repaint method is not available to me in Excel VBA.  Have I misunderstood how to use this method?  I'm not
using a userform, but just working with command buttons directly in the Excel spreadsheet.  Thanks in advance for any help.

I found this code on another post and it works perfectly... however (see below)

Private Declare Function FindWindowEx& Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$)
Private Declare Function ShowWindow& Lib "user32" (ByVal hwnd&, ByVal
nCmdShow&)

Sub TaskBar_Hide()
ShowWindow FindWindowEx(0, 0, "Shell_TrayWnd", vbNullString), 0
End Sub

Sub TaskBar_Show()
ShowWindow FindWindowEx(0, 0, "Shell_TrayWnd", vbNullString), 5
End Sub

However,
This seems to be setting a visible flag for the task bar. What I need is to
activate/deactivate the minimize of the task bar. (When you position the
mouse on the top line of the task bar, you get a double-headed arrow. when
you click-drag down, it minimizes the task bar. This leaves a small line
which is the top of the task bar that you can grab and drag up to un-minimize
the task bar).

How do I access that feature from VBA (using API or not)

I need this so that when I set my spreadsheet to show fullscreen, the status
bar gets hidden under the taskbar (becuse I want to leave it set "always on
top") I have found that manually minimizing then maximizing the taskbar
forces the status bar to show (I.E. the spreadsheet's full screen becomes
limited at the bottom to force both the taskbar and the (excel) statusbar to
be both visible.)

Also Im curious what the & in the above "FindWindowEx&" API call code does.
The code stops working when that character is removed.

--
Regards,
John

I am finalizing a research project that I have been working on in VBA and now only one little glitch remains. If I use Parent.Application.Visible to hide Excel so that user does not see the spreadsheets in the background it works fine until I do any I/O operation. When I open or save a file, the userform disappears behind any other application windows that are currently open. Since Excel is hidden there is no button in the taskbar to bring it back to the foreground which means that I have to minimize windows until I find the userform.

Obviously, this glitch is counter to the user friendliness that I am trying to achieve with this project and telling our clients that they have to minimize or close any other apps that they have running in order to run this app is not an option. It is very likely that this program will be run on the same computer that gathers data from the instrumentation at the chicken houses and that software must be kept up and running at all times.

Does anyone have any ideas as to why Excel is behaving this way an how to remedy this situation?

Also, late last week I started having an issue where the Toolbox palette appears when the program is executed. This is the VBA form toolbox palette so I am surprised that it appears on the spreadsheet side at all. I have, until recently, never seen this palette accessible outside of being in design mode for a form let alone visible while VBA code is running. Any ideas here?

Hi Leith
In your code the lines in red are not accepted in Excel 2007. Actually show up as RED color in the VBE.
Have I lost something from your code (AFAIK, this is original text).
'Written: October 07, 2007
'Author: Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000
Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

Private Declare Function GetWindowLong _
  Lib "user32.dll" _
   Alias "GetWindowLongA" _
    (ByVal hwnd As Long, ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "user32.dll" _
   Alias "SetWindowLongA" _
    (ByVal hwnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) As Long
     
'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
  Lib "user32.dll" _
   (ByVal hwnd As Long) As Long

'Returns the Window Handle of the Window accepting input
 Private Declare Function GetForegroundWindow _
  Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

 Dim BitMask As Long
 Dim Window_Handle As Long
 Dim WindowStyle As Long
 Dim Ret As Long

   If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
      Window_Handle = GetForegroundWindow()
  
       WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
       BitMask = WindowStyle Or Box_Type
  
      Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
      Ret = DrawMenuBar(Window_Handle)
   End If

End Sub
Use
Private Sub UserForm_Activate()
  AddToForm MIN_BOX
  AddToForm MAX_BOX
End Sub


I have an application that is composed of several add-ins that runs under XL97 - XL2003 because of the diversity of the user base. All of the add-ins are developed, compiled and saved under XL97.

I've found out the hard way that non-XL97 users sometimes get run-time errors because of where the add-ins were compiled. To address this problem, I've got initialization code in my main add-in that checks the XL version and if its not XL97 it dynamically recompiles all of the add-ins including itself. Happily this resolves the run-time errors.

Unfortunately, it causes some screen flashing during the recompilation. Normally this screen flashing is annoying but bearable but when Excel and the application are being served up via Citrix the screen flashing is really gross.

The code that does the recompile looks like this:

Set Book = Workbooks(BookName) ' Find our workbook/xla
With Application.VBE
If Err.Number = 0 And Not Book Is Nothing Then
If LCase(BookName) = LCase(Book.name) Then
On Error GoTo 0
' Make one of our components visible in the VBE
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Show
name = .VBProjects(Book.VBProject.name).name
' Find the Compile toolbar button.
With .CommandBars.FindControl(ID:=578)
If Mid(.caption, 10) = name Then
' Execute the compile if possible.
If .enabled = True Then
.Execute
DoEvents
CompileBook = True
End If
Else
LogIt "Not compiling workbook " & BookName & " but " & .caption
End If
End With
..VBProjects(Book.VBProject.name).VBComponents(1).CodeModule.CodePane.Window.Close
End If
End If
On Error GoTo 0
End With

The challenge is to get the focus to the correct VBA project without flashing the screen. As you can see, my code uses the .Show method of the CodePane object to accomplish this. I've tried using CodePane.Window.Visible = True but I've found it doesn't reliably set the focus to the desired project and so the wrong project gets compiled. .CodePane.Window.SetFocus seems to work as well as .Show but doesn't improve on the screen flashing.

The calls to the routine that perform the dynamic recompilation are bracketed by code that tries to freeze both the Excel and VBE windows. This code looks like this:

Application.ScreenUpdating = False
hWnd = myMainWhnd ' Get a handle to the application window
If hWnd <> 0 Then SendMessage hWnd, WM_SETREDRAW, -CLng(False), 0& ' Freeze the main Excel window
DoEvents ' Give it a chance to read the message
hWnd = myVBEWhnd ' Get a handle to the VBE window
If hWnd <> 0 Then LockWindowUpdate hWnd ' And lock it too

Without this code, the screen flashing is even worse than I described above.

Other things I've tried include:

- Minimizing the VBE .VBE.MainWindow.WindowState = vbext_ws_Minimize.

- Making the VBE really small application.VBE.MainWindow.Width & .Height = 0

- Looping through the VBE and closing all of its windows.

And as you might correctly guess, since I'm here, none of these things helped.

So I'm either looking for a different way to reliably dynamically recompile my various add-ins that doesn't cause the screen flash or a better way to suppress the screen flashing.

TIA for any suggestions.

josh


No luck finding an answer? You could always try Google.