Excel VBA

css navigation by Css3Menu.com

Use Registry for Settings

I had a case where the customer specified that the macros needed to be in the the template spreadsheet rather than the ‘write to’ as I would have written the project.

I decided to hold several settings in the Registry. One person always runs the macros, they siimply come from various members of the group.

Option Explicit

    Public Const APPNAME    As String = "CC Tracker"    '2nd
    Public Const RPTNAME    As String = "Tracker"  'Top
    Public Const KeyFROM    As String = "SpreadFrom"    'Key
    Public Const FROMtab    As String = "TabFrom"       'Data
    Public Const ToTab      As String = "ToTab"
    Public Const KeyTO      As String = "SpreadTo"
    Public Const USERID     As String = "User"

   Global CopyTo           As String
   Global CopyFrom         As String
   Dim Z                As Integer
   Global SheetTo          As String
   Global SheetFrom        As String

Sub InitTrackerLogCopy()
    'Capture settings prev used from PC registry
   CopyFrom = GetSetting(RPTNAME, APPNAME, KeyFROM, "x")    'Spread last from Registry
   SheetFrom = GetSetting(RPTNAME, APPNAME, FROMtab, "Reporting")   'Tab
   SheetTo = GetSetting(RPTNAME, APPNAME, ToTab)                    'Write to Tab
   SaveSetting RPTNAME, APPNAME, KeyFROM, ThisWorkbook.Name         'Save Current Book
   SaveSetting RPTNAME, APPNAME, USERID, Environ("USERNAME")        'Save User
   SaveSetting RPTNAME, APPNAME, "LastUse", Now()                   'Save Date
   CopyFrom = GetSetting(RPTNAME, APPNAME, KeyFROM, ThisWorkbook.Name)  'Get Cur Name
   CopyTo = GetSetting(RPTNAME, APPNAME, KeyTO, "The Log.xls")     'Get log name
   With frmCopyCharges
        With .lblCopyFrom
            .Caption = "Copy the spreadsheet from : " & vbCrLf _
                & Chr(32) & Chr(32) & CopyFrom          'Init form with spreadname
            .Font.Bold = True
            .Font.Name = "Arial Narrow"
            .Font.Size = 12
        End With


After the project was completed, several other groups took an interest and it has spread thru our building.

© 2011-2024

Updated:  01/23/2024 13:34
This page added:  11 September 2011