PDA

View Full Version : Solved: How to hide the Excel Icon in a custom menu bar



d4vem
06-13-2006, 12:52 PM
What I am doing is removing the standard Excel menu and creating custom menus. However the Excel icon is appearing at the start of the menu bar (next to Menu1) and I want to remove it. I have attached an example.

malik641
06-13-2006, 07:43 PM
Hi d4vem and Welcome :hi:

That logo is actually part of the worksheet window, you'll notice it dissappears when the worksheet is not maximized (Application.ActiveWindow.WindowState = xlNormal).

Thing is, I can't find any info on it anywhere... :dunno but I figured I'd at least tell you that it's not part of the menus so you don't go digging in the wrong area.

I'm going to keep looking for this :thumb

Hope I helped a little!!

MountainVogu
06-13-2006, 10:42 PM
Place this lot in a standard module and then call ChangeIcon



Option Explicit
Declare Function ExtractIcon Lib "shell32.dll" Alias "ExtractIconA" (ByVal hInst As Long, ByVal lpszExeFileName As String, ByVal nIconIndex As Long) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Long) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Const WM_SETICON = &H80
Private Const ICON_BIG = 1
Private Const ICON_SMALL = 0

Sub ChangeIcon()
Dim strIconPath As String
Dim lngXLHwnd As Long
Dim lngIcon As Long
Application.ScreenUpdating = False
Windows("Workbookname.xls"WindowState = xlNormal
strIconPath = ThisWorkbook.Path & "\app.ico"
lngXLHwnd = WorkbookHandle("Workbookname.xls")
lngIcon = ExtractIcon(0, strIconPath, 0)
SendMessage lngXLHwnd, WM_SETICON, ICON_SMALL, lngIcon
SendMessage lngXLHwnd, WM_SETICON, ICON_BIG, lngIcon
Application.Wait (Evaluate("=now()") + (0.25) / 86400)

'cant explain why doesn't fire until window normal and max twice
Windows("Workbookname.xls").WindowState = xlMaximized
Windows("Workbookname.xls").WindowState = xlNormal
Windows("Workbookname.xls").WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Function WorkbookHandle(strWBName As String) As Long
Dim dWnd As Long, hWnd As Long, mWnd As Long, cWnd As Long
dWnd = GetDesktopWindow
hWnd = FindWindowEx(dWnd, 0&, "XLMAIN", vbNullString)
mWnd = FindWindowEx(hWnd, 0&, "XLDESK", vbNullString)
While mWnd <> 0 And cWnd = 0
cWnd = FindWindowEx(mWnd, 0&, "EXCEL7", strWBName)
hWnd = FindWindowEx(dWnd, hWnd, "XLMAIN", vbNullString)
mWnd = FindWindowEx(hWnd, 0&, "XLDESK", vbNullString)
Wend
If cWnd > 0 Then
WorkbookHandle = cWnd
End If
End Function


This will change the Icon of the workbook to a given icon but not remove it, however if you create an empty Icon then use that.

If no icon is specified or the icon does not exist the default excel icon will be displayed.

d4vem
06-14-2006, 02:16 AM
I added the above and added application.run "ChangeIcon" into the auto_open routine and this didn't work it fails at the line Windows("Workbookname.xls"WindowState = xlNormal I changed this to Windows("Workbookname.xls").WindowState = xlNormal and it still failed any ideas?

Ivan F Moala
06-14-2006, 02:53 AM
the Windows("Workbookname.xls"WindowState = xlNormal

should read

Windows("Workbookname.xls").WindowState = xlNormal

d4vem
06-14-2006, 03:24 AM
I changed that and it still failed to work.

Justinlabenne
06-14-2006, 04:05 AM
This file includes a class module to configure the user environment (hiding all the commandbars, tooblars, etc...) and also a worksheet that you can fill out to create your own custom Worksheet Menu Bar. The excel environment configuring is still being tested, but it works.

To remove the icon from your menu bar you already have...you would need to just Protect the active window structure like this:

Public Sub RemoveWindowX()
ActiveWorkbook.Protect , , True
End Sub

Public Sub RestoreWindowX()
ActiveWorkbook.Protect , , False
End Sub



This will remove the [x] and the Excel icon.

johnske
06-14-2006, 04:09 AM
try this http://www.vbaexpress.com/kb/getarticle.php?kb_id=457

MountainVogu
06-14-2006, 06:47 AM
Sorry about that first attempt did it v. quickly.

The attached file will definitely work, just created the auto_open sub.

I have included the msn.ico.

Just extract both files and you should be right. This will change only the worksheet icon. Ensure the msn.ico is in same directory as the xls.

Mv

d4vem
06-14-2006, 01:40 PM
Still having problems. FYI I have already changed the main excel windows icon (not in the original example attached) but I am still getting the excel icon in the menu bar, that is what I am trying to overwrite.

MountainVogu
06-14-2006, 02:45 PM
Are we talking about the icon associated with the active workbook that get attached to the commandbar when maximized and shows on the workbook window when it is in xlnormal mode ?

If so the attachment supplied earlier will change that and only that icon.

not tested in anything other than xl 2002.

Oh and you don't need the application.wait line that was just in my original code, its just a 250ms delay

Further, if you have more than one workbook open and swap between them the icon on the menu will be change back to the default excel icon on the commandbar. But then if you xlnormal (restore window state) and then re-maximize your changed icon workbook window it will change back to the new icon. Excel, I presume only picks up the workbooks window icon in the restored state.

So you could add this to your changeicon.xls file under the thisworkbook part of the vba project


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If Windows(ThisWorkbook.Name).WindowState = xlMaximized Then
application.ScreenUpdating = False
Windows(ThisWorkbook.Name).WindowState = xlNormal
Windows(ThisWorkbook.Name).WindowState = xlMaximized
application.ScreenUpdating = True
End If
End Sub

apologies for any confusion

d4vem
06-14-2006, 04:16 PM
Attached picture reflects what I am trying to do.

Top picture has the normal window and menu bar excel icon. I have changed the window bar icon but cannot change the menu bar icon as per the second example. What I need to do is make the menu bar icon and the window bar icon the same regardless whether the visible sheet is in a minimised or maximised state.

Again thanks for your help

MountainVogu
06-14-2006, 04:50 PM
OK,

the attached file will change both the excel application Icon and the workbook icon of the workbook the code is placed in.

If you have any other workbooks open at the time or are opened through your menus why not create a class module to pick up the open_workbook event then you can change their window icons as well.

As stated there must be a valid icon not no icon.

d4vem
06-15-2006, 07:42 AM
Thanks for the assistance. Two questions;

1) When you state "Ensure the msn.ico is in same directory as the xls." Do you mean the same directory as the saved excel file?
2) Not familiar with class modules, if there is a better way for producing what I am trying to achieve then would be happy to change.

Thanks again

d4vem
06-28-2006, 04:28 AM
Thankyou for your solution it works fine

d4vem
06-28-2006, 06:41 AM
What I need to do now is remove the right click functionality when hovering over the custom icon in the menu bar as it still defaults to the excel standard. I have disabled the toolbar reset using the following code

Application.CommandBars("Toolbar List").Enabled = False

But it doesn't work on the icon. Any ideas?