Programmatically Resize Name Manager Dialog Box

G

Greg_M

New Member
Joined
Jan 28, 2017
Messages
15
Hi All,

I use Excel's built-in Name Manager a LOT!

Each time Excel is started, the dimensions, column widths etc. of the Name Manager dialog box are reset to default values which (I suppose!) are stored somewhere. The problem is that these default values are unsuitable for me, and I would like to be able to resize the dialog box programmatically.

I've performed a quick check of registry value changes but have been unable to identify where these settings might be stored.

So, does anyone know how to programmatically resize the Name Manager dialog box using e.g. API calls, registry values, other methods???

Regards,

Greg M

Please note that this item was originally posted on Excel Forum at: Programmatically Resize Name Manager Dialog Box
Click to expand...
 
Last edited by a moderator:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
I have looked in the Registry but, just like you, I didn't find where those settings might be stored.

I guess, one could use the windows API since the Name Manger header is a SysHeader32 window.

I'll give this a shot and if anything comes up, I'll post back.
 
Upvote 0
Hi AlphaFrog,

Many thanks for that. I actually use the JPK Name manager for "heavy lifting" where Names are concerned, but in many cases I just need the "quick 'n' easy" approach which the built-in manager provides. I should probably become familiar with how to do "quick 'n' easy" things using JPK's version! :)


Hi Jaafar,

When I posted this on ExcelForum, rorya's reply suggested that if anyone could provide a solution it would probably be you! :)

I hope you'll be pleased to see the acknowledgement that I include (where appropriate) in any workbooks which I distribute:

' I am happy to acknowledge that the assistance I received from the following site was invaluable in the creation and use of this Class Module:

' Cannot SetFocus > userform control
Click to expand...


Regards,

Greg M

 
Upvote 0
Just FYI, it's JKP, not JPK. :)
 
Upvote 0
Ok- Here is the deal:

Each time you want to modify the Name Manager dialog dimensions, column widths etc and want to keep the new settings , just run the Save_Name_Manager_Settings routine.

This routine will prompt you with the name manager dialog to which I have added a cutom button (Save Settings) accross the top.

After you have manually modified the settings and you are happy with their final look , just click the custom button located accross the top of the NM dialog and all the settings you entered will be stored (for later use) in a separate worksheet created on the fly.. The name of the storage sheet is (Name_Manager_Settings)

Now , everytime you mess with the dimensions, column widths etc in the NM dialog, you can restore your saved settings simply by running the Restore_Saved_Settings routine... You can conviniently call this routine from the workbook open event so everytime excel is started, your saved settings are restored automatically.

workbook demo

Here is a preview:








1- In a Standard Module: (Save settings code)
VBA Code: 
Option Explicit

Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Private Type HDITEM
   mask     As Long
   cxy      As Long
   pszText  As String
   hbm      As Long
   cchTextMax As Long
   fmt      As Long
   lParam   As Long
   iImage   As Long
   iOrder   As Long
End Type


#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongLong, ByVal nIndex As Long, ByVal dwNewLong As LongLong) As LongLong
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    #End If
    
    Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hwnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As Long
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" (ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName As String, ByVal dwStyle As Long, ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As LongPtr, ByVal hMenu As LongPtr, ByVal hInstance As LongPtr, lpParam As Any) As LongPtr
    Private Declare PtrSafe Function DestroyWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
    Private Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
    Private Declare PtrSafe Function GetClientRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long

    Private hHook As LongPtr, lPrevButtonProc As LongPtr, hNameManager  As LongPtr, hButton As LongPtr
    
#Else

    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" () 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 Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" (ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName As String, ByVal dwStyle As Long, ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As Long, ByVal hMenu As Long, ByVal hInstance As Long, lpParam As Any) As Long
    Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
    Private Declare Function GetClientRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
    
    Private hHook As Long, lPrevButtonProc As Long, hNameManager  As Long, hButton As Long

#End If


Private vSettingsValues(0 To 8, 2) As Variant
Private bSettingsUpdated As Boolean



Public Sub Save_Name_Manager_Settings()

    Const WH_CBT = 5

    Dim oSh As Worksheet, oCurrentSheet As Worksheet
    
    bSettingsUpdated = False
    
    Call UnhookWindowsHookEx(hHook)
    
    
    hHook = SetWindowsHookEx(WH_CBT, AddressOf HookProc, GetModuleHandle(vbNullString), GetCurrentThreadId)
    Application.Dialogs(xlDialogNameManager).Show
    Call UnhookWindowsHookEx(hHook)
    
    If bSettingsUpdated Then
    
        If Not SheetExists("Name_Manager_Settings") Then
            Set oCurrentSheet = ActiveSheet
            Application.EnableEvents = False
            Set oSh = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            oSh.Name = "Name_Manager_Settings"
            oCurrentSheet.Activate
            Application.EnableEvents = True
        End If
    
        With ThisWorkbook.Sheets("Name_Manager_Settings")
            .Range("A1:I2").Value = Transpose2DArray(vSettingsValues)
            .Columns("A:I").EntireColumn.AutoFit
            .Range("A1:I1").Font.Bold = True
        End With
        
        'ThisWorkbook.Save  '<==== Save the changes to Disk to preserve the new settings.
        
        MsgBox "Name Manager Settings Saved in Sheet: 'Name_Manager_Settings' .", vbInformation
        
    End If
    
End Sub


#If Win64 Then
    Private Function HookProc(ByVal lCode As Long, ByVal wParam As LongLong, ByVal lParam As LongLong) As LongLong
        Dim hListViewParent As LongLong
#Else
    Private Function HookProc(ByVal lCode As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
        Dim hListViewParent As Long
#End If

    Const MAX_PATH = 260
    Const WS_CHILD = &H40000000
    Const WS_VISIBLE As Long = &H10000000
    Const GWL_WNDPROC As Long = -4
    Const HCBT_ACTIVATE = 5
    Const HC_ACTION = 0
    
    Dim tRect As RECT
    Dim sClassName As String * MAX_PATH, lBuff As Long, lRet As Long
    
    If lCode < HC_ACTION Then
        HookProc = CallNextHookEx(hHook, lCode, wParam, lParam)
        Exit Function
    End If
    
    If lCode = HCBT_ACTIVATE Then
        lBuff = MAX_PATH
        lRet = GetClassName(wParam, sClassName, lBuff)
        If Left(sClassName, lRet) = "bosa_sdm_XL9" Then
            Call UnhookWindowsHookEx(hHook)
            hNameManager = wParam
            hListViewParent = FindWindowEx(wParam, 0, "XLLVP", vbNullString)
            Call GetClientRect(wParam, tRect)
            hButton = CreateWindowEx(0, "Button", "Save Settings", WS_CHILD + WS_VISIBLE, _
            tRect.Left + 300, tRect.Top + 5, 100, 25, wParam, 0, GetModuleHandle(vbNullString), 0)
            lPrevButtonProc = SetWindowLong(hButton, GWL_WNDPROC, AddressOf ButtonProc)
        End If
    End If
    
    Call CallNextHookEx(hHook, lCode, wParam, lParam)
    
End Function



#If Win64 Then
    Private Function ButtonProc(ByVal hwnd As LongLong, ByVal Msg As Long, ByVal wParam As LongLong, ByVal lParam As LongLong) As LongLong
#Else
    Private Function ButtonProc(ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
 
    Const WM_LBUTTONUP = &H202
    Const WM_SYSCOMMAND = &H112
    Const SC_CLOSE = &HF060&
    Const GWL_WNDPROC As Long = -4
    
    Select Case Msg
        Case WM_LBUTTONUP
            Call GetNameManagerSettings
            Call SetWindowLong(hwnd, GWL_WNDPROC, lPrevButtonProc)
            Call DestroyWindow(hwnd)
            Call PostMessage(hNameManager, WM_SYSCOMMAND, SC_CLOSE, ByVal 0)
            bSettingsUpdated = True
    End Select
    
    ButtonProc = CallWindowProc(lPrevButtonProc, hwnd, Msg, wParam, ByVal lParam)
 
End Function



Private Sub GetNameManagerSettings()

    Const HDM_FIRST = &H1200
    Const HDM_GETITEMCOUNT = (HDM_FIRST + 0)
    Const HDM_GETITEMA = (HDM_FIRST + 3)
    Const HDM_GETITEM = HDM_GETITEMA
    Const HDF_STRING = &H4000
    Const HDI_TEXT = 2
    Const LVM_FIRST = &H1000
    Const LVM_GETCOLUMNWIDTH = LVM_FIRST + 29
    Const MAX_PATH = 260
    
    #If Win64 Then
        Dim hwnd As LongLong, hParent As LongLong
        Dim lColumn As LongLong, lCols As LongLong, lRet As LongLong
    #Else
        Dim hwnd As Long, hParent As Long
        Dim lColumn As Long, lCols As Long, lRet As Long
    #End If

    Dim tHd As HDITEM
    Dim tWinRect As RECT
    Dim sBuffer As String * MAX_PATH
    

    hwnd = FindWindowEx(hNameManager, 0, "XLLVP", vbNullString)
    hParent = FindWindowEx(hwnd, 0, "SysListView32", vbNullString)
    hwnd = FindWindowEx(hParent, 0, "SysHeader32", vbNullString)
            
    If hParent Then
        lCols = SendMessage(hwnd, HDM_GETITEMCOUNT, 0, 0)
        Call GetWindowRect(hNameManager, tWinRect)
        With tWinRect
            vSettingsValues(0, 1) = "Left": vSettingsValues(0, 2) = .Left
            vSettingsValues(1, 1) = "Top": vSettingsValues(1, 2) = .Top
            vSettingsValues(2, 1) = "Width": vSettingsValues(2, 2) = .Right - .Left
            vSettingsValues(3, 1) = "Height": vSettingsValues(3, 2) = .Bottom - .Top
        End With
        For lColumn = 0 To lCols - 1
            Call SendMessage(hParent, LVM_GETCOLUMNWIDTH, CLng(lColumn), ByVal 0)
            With tHd
                .mask = HDI_TEXT
                .cchTextMax = MAX_PATH
                .pszText = sBuffer
                .fmt = HDF_STRING
            End With
            lRet = SendMessage(hwnd, HDM_GETITEM, CLng(lColumn), tHd)
            If lRet Then
                vSettingsValues(CLng(lColumn) + 4, 1) = StripNulls(Left(tHd.pszText, MAX_PATH))
                vSettingsValues(CLng(lColumn) + 4, 2) = SendMessage(hParent, LVM_GETCOLUMNWIDTH, CLng(lColumn), ByVal 0)
            End If
        Next
    End If


End Sub

Private Function StripNulls(Str As String) As String
    If InStr(Str, Chr(0)) Then
        Str = Left(Str, InStr(Str, Chr(0)) - 1)
    End If
    StripNulls = Str
End Function


Private Function SheetExists(ByVal SheetName As String) As Boolean
    On Error Resume Next
        SheetExists = Not CBool(Sheets(SheetName) Is Nothing)
    On Error GoTo 0
End Function


Private Function Transpose2DArray(InputArray As Variant) As Variant

    Dim X As Long, yUbound As Long
    Dim Y As Long, xUbound As Long
    Dim vTempArray As Variant

    xUbound = UBound(InputArray, 2)
    yUbound = UBound(InputArray, 1)
    
    ReDim vTempArray(1 To xUbound, 0 To yUbound)
    
    For X = 1 To xUbound
        For Y = 0 To yUbound
            vTempArray(X, Y) = InputArray(Y, X)
        Next Y
    Next X
    
    Transpose2DArray = vTempArray
    
End Function



2- In a Standard Module: (Restore setting code)
VBA Code: 
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As LongPtr
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As Long
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function MoveWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
    
    Private hHook As LongPtr
#Else
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount 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 Declare Function MoveWindow Lib "user32" (ByVal hwnd As Long, ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    
    Private hHook As Long
#End If


Private vSettingsValues(9) As Variant



Public Sub Restore_Saved_Settings()

    Const WH_CBT = 5
    Dim i As Long
    
    If SheetExists("Name_Manager_Settings") Then
        With ThisWorkbook.Sheets("Name_Manager_Settings")
            For i = LBound(vSettingsValues) To UBound(vSettingsValues)
                vSettingsValues(i) = ThisWorkbook.Sheets("Name_Manager_Settings").Cells(2, i + 1).Value
            Next i
        End With
        
        Call UnhookWindowsHookEx(hHook)
        hHook = SetWindowsHookEx(WH_CBT, AddressOf HookProc, GetModuleHandle(vbNullString), GetCurrentThreadId)
        Application.Dialogs(xlDialogNameManager).Show
        Call UnhookWindowsHookEx(hHook)
    Else
        MsgBox "There are no settings to restore." & vbCrLf & "Save the settings first.", vbExclamation
    End If

End Sub



#If Win64 Then
    Private Function HookProc(ByVal lCode As Long, ByVal wParam As LongLong, ByVal lParam As LongLong) As LongLong
        Dim hwnd As LongLong, hParent As LongLong
        Dim lCols As LongLong, lColumn As LongLong
#Else
    Private Function HookProc(ByVal lCode As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
        Dim hwnd As Long, hParent As Long
        Dim lCols As Long, lColumn As Long
#End If

    Const HDM_FIRST = &H1200
    Const HDM_GETITEMCOUNT = (HDM_FIRST + 0)
    Const LVM_FIRST = &H1000
    Const LVM_SETCOLUMNWIDTH = LVM_FIRST + 30
    Const MAX_PATH = 260
    Const HCBT_ACTIVATE = 5
    Const HC_ACTION = 0
    Const WM_SYSCOMMAND = &H112
    Const SC_CLOSE = &HF060&

    Dim lColWidth As Long
    Dim sClassName As String * MAX_PATH, lBuff As Long, lRet As Long

    If lCode < HC_ACTION Then
        HookProc = CallNextHookEx(hHook, lCode, wParam, lParam)
        Exit Function
    End If

    If lCode = HCBT_ACTIVATE Then
        lBuff = MAX_PATH
        lRet = GetClassName(wParam, sClassName, lBuff)
        If Left(sClassName, lRet) = "bosa_sdm_XL9" Then
            Call MoveWindow(wParam, vSettingsValues(0), vSettingsValues(1), vSettingsValues(2), vSettingsValues(3), 1)
            hwnd = FindWindowEx(wParam, 0, "XLLVP", vbNullString)
            hParent = FindWindowEx(hwnd, 0, "SysListView32", vbNullString)
            hwnd = FindWindowEx(hParent, 0, "SysHeader32", vbNullString)
            lCols = SendMessage(hwnd, HDM_GETITEMCOUNT, 0, 0)
            For lColumn = 0 To lCols - 1
                lColWidth = vSettingsValues(CLng(lColumn) + 4)
                Call SendMessage(hParent, LVM_SETCOLUMNWIDTH, CLng(lColumn), ByVal lColWidth)
            Next
            Call PostMessage(wParam, WM_SYSCOMMAND, SC_CLOSE, ByVal 0)
        End If
    End If
    
    Call CallNextHookEx(hHook, lCode, wParam, lParam)
    
End Function


Private Function SheetExists(ByVal SheetName As String) As Boolean
    On Error Resume Next
        SheetExists = Not CBool(Sheets(SheetName) Is Nothing)
    On Error GoTo 0
End Function



You can put this in the ThisWorkbook Module:
VBA Code: 
Option Explicit

Private Sub Workbook_Open()
    Call Restore_Saved_Settings
End Sub
 
Upvote 0
Hi rorya,

Indeed it is! Funny how Jan Pieter Karelse sounds just as "ok" as Jan Karel Pieterse :)

Regards,

Greg M
 
Upvote 0
Delighted to see my faith in Jaafar was well placed, as expected. :)
 
Upvote 0
Hi again Jaafar,

Holy Moly! That was impressive and FAST!!!

I just wanted to acknowledge your response immediately. I have to leave my computer now, but hope to be able to incorporate your suggestion later on today. I will certainly let you know how I get on, but (given your reputation! :):):)) I don't anticipate too many problems.

Thanks and best regards,

Greg M
 
Upvote 0
Hi again Jaafar,

This is absolutely fantastic! Thank you so much for your solution.

You can conviniently call this routine from the workbook open event so everytime excel is started, your saved settings are restored automatically.
Click to expand...

Actually, I made an "every time" copy of your workbook which I have stored in the Excel StartUp folder. I've slightly modified your Workbook_Open routine so that the workbook closes as soon as my preferred Name Manager settings have been loaded:

VBA Code: 
Private Sub Workbook_Open()

    Call Restore_Saved_Settings

    ThisWorkbook.Close

End Sub

I keep an extra copy of your workbook (without the above modification) in my "Excel Toolbox" folder so that I can make one-session-only changes if required.


One (small?) further request if possible. The code which creates the "Save Settings" button is as follows:

VBA Code: 
            hButton = CreateWindowEx(0, "Button", "Save Settings", WS_CHILD + WS_VISIBLE, _
            tRect.Left + 300, tRect.Top + 5, 100, 25, wParam, 0, GetModuleHandle(vbNullString), 0)

The "Save Settings" text on the above button is very small (I think it's possibly due to my graphics card / monitor combination, because this sometimes occurs in other situations also), so is there any parameter which can be tweaked to increase the font size? No problem at all if that's a big deal, but (as I always tell my clients) there's no extra charge for just asking. :)

Once again many thanks, and best regards,

Greg M
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Show the "Edit Name" dialog from VBA for a specific Name
Replies
1
Views
361
Jaafar Tribak
Jaafar Tribak
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
130
Anthony47
A
A
  • Question
VBA Code help: TO: CC: BCC in Mail Merge
Replies
0
Views
2K
agutosay
A
J
  • Question
Scraping data from WIFI networks that are available
9 10 11
Replies
109
Views
5K
Prome3
P
A
  • Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A

Forum statistics

Threads
1,214,806
Messages
6,121,667
Members
449,045
Latest member
Marcus05

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top