1

I have developed a macro for my work that will automatically generate a report we have to hand off every day. The macro prompts the user to provide our bank report (in .CSV format) and our accounting system's report (Great Plains - Also .CSV). The macro takes these two files, creates a new workbook, pastes them into two separate sheets and calculates the discrepancies and alerts the user.

After doing this, the user is to post all payments from GP to lock in any deposits we have received for the date in question. This macro has changed our manual process into a digital only (we used to print off the report after manually creating it, now the macro saves it onto our network drive so we no longer have to pass the papers off, including what has been 'posted')

Since this is a new process, I also created a MsgBox to alert the user to save all postings to a specific file-path. This is for those who are unfamiliar with the process. I want it to allow the user to check a box to Never show this message again, so when they run the report the next time, the message won't display.

I assume to do this, I will have to convert the message from a MsgBox to a UserForm. The macro (template) file is saved on the network drive, rather than a particular machine.

My question: Is there a way to save the user's input, or rather save a setting to that particular machine? Is there a way to call for a machine's information and save the information to a list, or save the setting to that machine?

The Prompt:

MsgBox "STOP!" & vbCr & vbCr & _
    "When prompted to print upon closing the Bank Deposit screen, deselect " & _
    "the " & Chr(34) & "Print" & Chr(34) & " option and select the " & _
    Chr(34) & "File" & Chr(34) & " option. Save the file as a " & _
    "Comma-delimited file in the necessary file on the accouting drive.", _
    vbCritical, "Posting in Great Plains"`
Community
  • 1
  • 1
Munkeeface
  • 411
  • 3
  • 11

1 Answers1

4

You can store the user choice in registry.

Here is an example, you can rework your code along this.

Sub test()

    '/ Check if setting already exists
    If GetSetting("Test_App", "Show", "MsgBox") <> "Yes" Then
        '/ Show the message box
        If MsgBox("Don't Show this to me again..", vbYesNo + vbCritical, "Test") = vbYes Then
            '/ Save the setting in registery
            SaveSetting "Test_App", "Show", "MsgBox", "Yes"
        End If
    End If


End Sub
cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • 1
    A cell also can be used to store that information in case the file is used on different computers, and verified upon workbook Open event. – Hadi Jul 14 '16 at 17:00
  • I'm a tad new to VBA, but looking at the MSDN for the Get/SaveSetting functions, the parameters are throwing me for a loop. Would this be essentially 3 strings to help narrow down the setting 'ID'? – Munkeeface Jul 14 '16 at 17:25
  • Every time anyone opens the file, it's from the network drive specifically. No one saves it on their system unless it's a shortcut to the file-path. Is there a way to get the specific machine information to store in this function? i.e. `SaveSetting(_macro_, _machine_, _msgBoxSetting_) – Munkeeface Jul 14 '16 at 17:29
  • 1
    Machine name : http://stackoverflow.com/questions/3551055/getting-computer-name-using-vba User name : http://stackoverflow.com/questions/13412418/how-can-i-display-my-windows-user-name-in-excel-spread-sheet-using-macros – cyboashu Jul 14 '16 at 17:35
  • Thank you all so much! @cyboashu would you like to tag this on your answer, as it answers the second part of the question? – Munkeeface Jul 14 '16 at 17:46