Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide the Name Box
I found this code posted by Rick Rothstein a while back, and it does nearly
everything for which I was looking. I have one more thing I'd like to change about it though and cannot seem to find out how, or if it is possible. The routine EnableNameComboBox below does exactly what it says it does, and it works flawlessly. However, when the Name Box is disabled, the arrow, (which is ordinarily used to dropdown the list of names remains visible). The comment in the code referring to "Make the dropdown list 200 pixels wide" is what has me puzzled... where is it set at 200 pixels? and is there a way to widen it, such that the arrow will be hidden as well? Or is there an alternative method to obscuring that arrow? Rick's former post: 'Add a Module and copy/paste all the code after my signature into its code window. 'To use it, simply execute this code... 'To disable Name Box: EnableNameComboBox False 'To re-enable Name Box: EnableNameComboBox True 'Note: I have cobbled this together from many different sources. 'I have left in (or expanded on) the comments that were included with the 'original code in case you want to "tinker" with the code some. ' Enables or disables a window. If a window is disabled, it cannot ' receive the focus and will ignore any attempted input. Some types ' of windows, such as buttons and other controls, will appear grayed ' when disabled, although any window can be enabled or disabled. The ' function returns 0 if the window had previously been enabled, or a ' non-zero value if the window had been disabled. Private Declare Function EnableWindow Lib "user32.dll" _ (ByVal hwnd As Long, _ ByVal fEnable As Long) As Long ' Get the handle of the desktop window Private Declare Function GetDesktopWindow Lib "user32" () As Long ' Get the process ID of this instance of Excel Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long ' Get the ID of the process that a window belongs to Private Declare Function GetWindowThreadProcessId Lib "user32" _ (ByVal hwnd As Long, _ ByRef lpdwProcessId As Long) As Long Private 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 Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_SETTEXT As Long = 12& 'Make the Name dropdown list 200 pixels wide Public Sub EnableNameComboBox(State As Boolean) Dim hWndFormulaBar As Long Dim hWndNameCombo As Long 'Get the handle for the formula bar window hWndFormulaBar = FindWindowEx(ApphWnd(), 0, "EXCEL;", vbNullString) 'Get the handle for the Name combobox hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, "combobox", vbNullString) ' Clear the NAME box's edit field SendMessage hWndNameCombo, WM_SETTEXT, ByVal 0, ByVal "" ' Disable the NAME box EnableWindow hWndNameCombo, State End Sub ' Get the main Excel window's hWnd Private Function ApphWnd() As Long 'Excel 2002 and above have a property for the hWnd If Val(Application.Version) = 10 Then ApphWnd = Application.hwnd Else ApphWnd = FindOurWindow("XLMAIN", Application.Caption) End If End Function ' Finds a top-level window of the given class and caption that ' belongs to this instance of Excel, by matching the process IDs Private Function FindOurWindow(Optional sClass As String = vbNullString, _ Optional sCaption As String = vbNullString) Dim hWndDesktop As Long Dim hwnd As Long Dim hProcThis As Long Dim hProcWindow As Long 'Get the ID of this instance of Excel, to match to hProcThis = GetCurrentProcessId ' All top-level windows are children of the desktop, ' so get that handle first hWndDesktop = GetDesktopWindow Do ' Find the next child window of the desktop that matches the given ' window class and/or caption. The first time in, hWnd will be zero, ' so we'll get the first matching window. Each call will pass the ' handle of the window we found the last time, thereby getting the ' next one (if any) hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption) ' Get the ID of the process that owns the window GetWindowThreadProcessId hwnd, hProcWindow ' Loop until the window's process matches this process, ' or we didn't find a window Loop Until hProcWindow = hProcThis Or hwnd = 0 ' Return the handle we found FindOurWindow = hwnd End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide the Name Box
I can answer the "200 pixel" question for you. The first sentence in my
"Note" contained in the first comments says... "'Note: I have cobbled this together from many different sources." Unfortunately, that comment apparently came from one of the routines I cobbled the code together from and should have been removed prior to my having posted it. Sorry if that caused you any confusion. As for the "hide the arrow" question... I am unaware of a method to do this. Hopefully one of the other volunteers here will already know if it is possible to do or not. -- Rick (MVP - Excel) "NYDean" wrote in message ... I found this code posted by Rick Rothstein a while back, and it does nearly everything for which I was looking. I have one more thing I'd like to change about it though and cannot seem to find out how, or if it is possible. The routine EnableNameComboBox below does exactly what it says it does, and it works flawlessly. However, when the Name Box is disabled, the arrow, (which is ordinarily used to dropdown the list of names remains visible). The comment in the code referring to "Make the dropdown list 200 pixels wide" is what has me puzzled... where is it set at 200 pixels? and is there a way to widen it, such that the arrow will be hidden as well? Or is there an alternative method to obscuring that arrow? Rick's former post: 'Add a Module and copy/paste all the code after my signature into its code window. 'To use it, simply execute this code... 'To disable Name Box: EnableNameComboBox False 'To re-enable Name Box: EnableNameComboBox True 'Note: I have cobbled this together from many different sources. 'I have left in (or expanded on) the comments that were included with the 'original code in case you want to "tinker" with the code some. ' Enables or disables a window. If a window is disabled, it cannot ' receive the focus and will ignore any attempted input. Some types ' of windows, such as buttons and other controls, will appear grayed ' when disabled, although any window can be enabled or disabled. The ' function returns 0 if the window had previously been enabled, or a ' non-zero value if the window had been disabled. Private Declare Function EnableWindow Lib "user32.dll" _ (ByVal hwnd As Long, _ ByVal fEnable As Long) As Long ' Get the handle of the desktop window Private Declare Function GetDesktopWindow Lib "user32" () As Long ' Get the process ID of this instance of Excel Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long ' Get the ID of the process that a window belongs to Private Declare Function GetWindowThreadProcessId Lib "user32" _ (ByVal hwnd As Long, _ ByRef lpdwProcessId As Long) As Long Private 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 Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_SETTEXT As Long = 12& 'Make the Name dropdown list 200 pixels wide Public Sub EnableNameComboBox(State As Boolean) Dim hWndFormulaBar As Long Dim hWndNameCombo As Long 'Get the handle for the formula bar window hWndFormulaBar = FindWindowEx(ApphWnd(), 0, "EXCEL;", vbNullString) 'Get the handle for the Name combobox hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, "combobox", vbNullString) ' Clear the NAME box's edit field SendMessage hWndNameCombo, WM_SETTEXT, ByVal 0, ByVal "" ' Disable the NAME box EnableWindow hWndNameCombo, State End Sub ' Get the main Excel window's hWnd Private Function ApphWnd() As Long 'Excel 2002 and above have a property for the hWnd If Val(Application.Version) = 10 Then ApphWnd = Application.hwnd Else ApphWnd = FindOurWindow("XLMAIN", Application.Caption) End If End Function ' Finds a top-level window of the given class and caption that ' belongs to this instance of Excel, by matching the process IDs Private Function FindOurWindow(Optional sClass As String = vbNullString, _ Optional sCaption As String = vbNullString) Dim hWndDesktop As Long Dim hwnd As Long Dim hProcThis As Long Dim hProcWindow As Long 'Get the ID of this instance of Excel, to match to hProcThis = GetCurrentProcessId ' All top-level windows are children of the desktop, ' so get that handle first hWndDesktop = GetDesktopWindow Do ' Find the next child window of the desktop that matches the given ' window class and/or caption. The first time in, hWnd will be zero, ' so we'll get the first matching window. Each call will pass the ' handle of the window we found the last time, thereby getting the ' next one (if any) hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption) ' Get the ID of the process that owns the window GetWindowThreadProcessId hwnd, hProcWindow ' Loop until the window's process matches this process, ' or we didn't find a window Loop Until hProcWindow = hProcThis Or hwnd = 0 ' Return the handle we found FindOurWindow = hwnd End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide the Name Box
Thanks Rick,
Yes that did have me scratching my head... And, fwiw, I have since learned that as well as that Name Box hiding routine worked in XL 2003, it does not seem to work on XL 2007. Do you, or anyone, know how to do something similar in XL 2007? Anyhow, Thanks again "Rick Rothstein" wrote: I can answer the "200 pixel" question for you. The first sentence in my "Note" contained in the first comments says... "'Note: I have cobbled this together from many different sources." Unfortunately, that comment apparently came from one of the routines I cobbled the code together from and should have been removed prior to my having posted it. Sorry if that caused you any confusion. As for the "hide the arrow" question... I am unaware of a method to do this. Hopefully one of the other volunteers here will already know if it is possible to do or not. -- Rick (MVP - Excel) "NYDean" wrote in message ... I found this code posted by Rick Rothstein a while back, and it does nearly everything for which I was looking. I have one more thing I'd like to change about it though and cannot seem to find out how, or if it is possible. The routine EnableNameComboBox below does exactly what it says it does, and it works flawlessly. However, when the Name Box is disabled, the arrow, (which is ordinarily used to dropdown the list of names remains visible). The comment in the code referring to "Make the dropdown list 200 pixels wide" is what has me puzzled... where is it set at 200 pixels? and is there a way to widen it, such that the arrow will be hidden as well? Or is there an alternative method to obscuring that arrow? Rick's former post: 'Add a Module and copy/paste all the code after my signature into its code window. 'To use it, simply execute this code... 'To disable Name Box: EnableNameComboBox False 'To re-enable Name Box: EnableNameComboBox True 'Note: I have cobbled this together from many different sources. 'I have left in (or expanded on) the comments that were included with the 'original code in case you want to "tinker" with the code some. ' Enables or disables a window. If a window is disabled, it cannot ' receive the focus and will ignore any attempted input. Some types ' of windows, such as buttons and other controls, will appear grayed ' when disabled, although any window can be enabled or disabled. The ' function returns 0 if the window had previously been enabled, or a ' non-zero value if the window had been disabled. Private Declare Function EnableWindow Lib "user32.dll" _ (ByVal hwnd As Long, _ ByVal fEnable As Long) As Long ' Get the handle of the desktop window Private Declare Function GetDesktopWindow Lib "user32" () As Long ' Get the process ID of this instance of Excel Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long ' Get the ID of the process that a window belongs to Private Declare Function GetWindowThreadProcessId Lib "user32" _ (ByVal hwnd As Long, _ ByRef lpdwProcessId As Long) As Long Private 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 Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_SETTEXT As Long = 12& 'Make the Name dropdown list 200 pixels wide Public Sub EnableNameComboBox(State As Boolean) Dim hWndFormulaBar As Long Dim hWndNameCombo As Long 'Get the handle for the formula bar window hWndFormulaBar = FindWindowEx(ApphWnd(), 0, "EXCEL;", vbNullString) 'Get the handle for the Name combobox hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, "combobox", vbNullString) ' Clear the NAME box's edit field SendMessage hWndNameCombo, WM_SETTEXT, ByVal 0, ByVal "" ' Disable the NAME box EnableWindow hWndNameCombo, State End Sub ' Get the main Excel window's hWnd Private Function ApphWnd() As Long 'Excel 2002 and above have a property for the hWnd If Val(Application.Version) = 10 Then ApphWnd = Application.hwnd Else ApphWnd = FindOurWindow("XLMAIN", Application.Caption) End If End Function ' Finds a top-level window of the given class and caption that ' belongs to this instance of Excel, by matching the process IDs Private Function FindOurWindow(Optional sClass As String = vbNullString, _ Optional sCaption As String = vbNullString) Dim hWndDesktop As Long Dim hwnd As Long Dim hProcThis As Long Dim hProcWindow As Long 'Get the ID of this instance of Excel, to match to hProcThis = GetCurrentProcessId ' All top-level windows are children of the desktop, ' so get that handle first hWndDesktop = GetDesktopWindow Do ' Find the next child window of the desktop that matches the given ' window class and/or caption. The first time in, hWnd will be zero, ' so we'll get the first matching window. Each call will pass the ' handle of the window we found the last time, thereby getting the ' next one (if any) hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption) ' Get the ID of the process that owns the window GetWindowThreadProcessId hwnd, hProcWindow ' Loop until the window's process matches this process, ' or we didn't find a window Loop Until hProcWindow = hProcThis Or hwnd = 0 ' Return the handle we found FindOurWindow = hwnd End Function . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide the Name Box
That code works for me in my copy of XL2007.
-- Rick (MVP - Excel) "NYDean" wrote in message ... Thanks Rick, Yes that did have me scratching my head... And, fwiw, I have since learned that as well as that Name Box hiding routine worked in XL 2003, it does not seem to work on XL 2007. Do you, or anyone, know how to do something similar in XL 2007? Anyhow, Thanks again "Rick Rothstein" wrote: I can answer the "200 pixel" question for you. The first sentence in my "Note" contained in the first comments says... "'Note: I have cobbled this together from many different sources." Unfortunately, that comment apparently came from one of the routines I cobbled the code together from and should have been removed prior to my having posted it. Sorry if that caused you any confusion. As for the "hide the arrow" question... I am unaware of a method to do this. Hopefully one of the other volunteers here will already know if it is possible to do or not. -- Rick (MVP - Excel) "NYDean" wrote in message ... I found this code posted by Rick Rothstein a while back, and it does nearly everything for which I was looking. I have one more thing I'd like to change about it though and cannot seem to find out how, or if it is possible. The routine EnableNameComboBox below does exactly what it says it does, and it works flawlessly. However, when the Name Box is disabled, the arrow, (which is ordinarily used to dropdown the list of names remains visible). The comment in the code referring to "Make the dropdown list 200 pixels wide" is what has me puzzled... where is it set at 200 pixels? and is there a way to widen it, such that the arrow will be hidden as well? Or is there an alternative method to obscuring that arrow? Rick's former post: 'Add a Module and copy/paste all the code after my signature into its code window. 'To use it, simply execute this code... 'To disable Name Box: EnableNameComboBox False 'To re-enable Name Box: EnableNameComboBox True 'Note: I have cobbled this together from many different sources. 'I have left in (or expanded on) the comments that were included with the 'original code in case you want to "tinker" with the code some. ' Enables or disables a window. If a window is disabled, it cannot ' receive the focus and will ignore any attempted input. Some types ' of windows, such as buttons and other controls, will appear grayed ' when disabled, although any window can be enabled or disabled. The ' function returns 0 if the window had previously been enabled, or a ' non-zero value if the window had been disabled. Private Declare Function EnableWindow Lib "user32.dll" _ (ByVal hwnd As Long, _ ByVal fEnable As Long) As Long ' Get the handle of the desktop window Private Declare Function GetDesktopWindow Lib "user32" () As Long ' Get the process ID of this instance of Excel Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long ' Get the ID of the process that a window belongs to Private Declare Function GetWindowThreadProcessId Lib "user32" _ (ByVal hwnd As Long, _ ByRef lpdwProcessId As Long) As Long Private 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 Declare Function SendMessage Lib "user32" _ Alias "SendMessageA" _ (ByVal hwnd As Long, _ ByVal wMsg As Long, _ ByVal wParam As Long, _ lParam As Any) As Long Private Const WM_SETTEXT As Long = 12& 'Make the Name dropdown list 200 pixels wide Public Sub EnableNameComboBox(State As Boolean) Dim hWndFormulaBar As Long Dim hWndNameCombo As Long 'Get the handle for the formula bar window hWndFormulaBar = FindWindowEx(ApphWnd(), 0, "EXCEL;", vbNullString) 'Get the handle for the Name combobox hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, "combobox", vbNullString) ' Clear the NAME box's edit field SendMessage hWndNameCombo, WM_SETTEXT, ByVal 0, ByVal "" ' Disable the NAME box EnableWindow hWndNameCombo, State End Sub ' Get the main Excel window's hWnd Private Function ApphWnd() As Long 'Excel 2002 and above have a property for the hWnd If Val(Application.Version) = 10 Then ApphWnd = Application.hwnd Else ApphWnd = FindOurWindow("XLMAIN", Application.Caption) End If End Function ' Finds a top-level window of the given class and caption that ' belongs to this instance of Excel, by matching the process IDs Private Function FindOurWindow(Optional sClass As String = vbNullString, _ Optional sCaption As String = vbNullString) Dim hWndDesktop As Long Dim hwnd As Long Dim hProcThis As Long Dim hProcWindow As Long 'Get the ID of this instance of Excel, to match to hProcThis = GetCurrentProcessId ' All top-level windows are children of the desktop, ' so get that handle first hWndDesktop = GetDesktopWindow Do ' Find the next child window of the desktop that matches the given ' window class and/or caption. The first time in, hWnd will be zero, ' so we'll get the first matching window. Each call will pass the ' handle of the window we found the last time, thereby getting the ' next one (if any) hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption) ' Get the ID of the process that owns the window GetWindowThreadProcessId hwnd, hProcWindow ' Loop until the window's process matches this process, ' or we didn't find a window Loop Until hProcWindow = hProcThis Or hwnd = 0 ' Return the handle we found FindOurWindow = hwnd End Function . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you hide/un-hide the grid lines | Excel Discussion (Misc queries) | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Specify which rows to NOT hide, and have excel hide the rest | Excel Programming | |||
Hide And Un-hide Excel Toolbars | Excel Programming | |||
How do I hide a worksheet in Excel and use a password to un-hide . | Excel Discussion (Misc queries) |