Hello Forum,

I am working on designing a chemical inventory using Excel and VBA. One of the functions within the spreadsheet is to "inquire" about a chemical which emails a respective group's outlook distribution list based on the chemical record's details.

My problem arises from the inherit limitation of not being able to share a macro enabled document. My work around for this problem was to create multiple chemical inventories (~13, one for each laboratory group) and have each record export a *.csv file to a shared location when a save event is triggered. When determined necessary, a "site-wide" chemical inventory can be "refreshed" which compiles all the various saved *.csv files into their workbook for a "master" view.

The piece that I see giving some issue is updating the code of ~13 or so excel files when a small change occurs. More specifically, this section in the code is likely to change from time to time:

If Group = "160N-517" Then TeamName = "Biophysical Characterization and MS-S Inorganics"
If Group = "175" Then TeamName = "QCCA"
If Group = "120N" Then TeamName = "Bio"
If Group = "160N-417" Then TeamName = "Extractable and Leachable"
If Group = "140" Then TeamName = "Pulmonary and Nasal"
If Group = "140" Then TeamName = "Metals"
If Group = "160N-317" Then TeamName = "Stability"
If Group = "160N-220" Then TeamName = "Stability"
If Group = "160N-318" Then TeamName = "Stability"
If Group = "160N-518" Then TeamName = "Stability"
If Group = "160N-218" Then TeamName = "Product Venture Group"
If Group = "160S-230" Then TeamName = "Sterile Analytical or Analytical Research and Development"

If Group = "160N-517" Then Blaster = "G-ST-RTP-Inventory-BPC-MS-S"
If Group = "175" Then Blaster = "G-ST-RTP-Inventory-QCCA"
If Group = "120N" Then Blaster = "G-ST-RTP-Inventory-Bio"
If Group = "160N-417" Then Blaster = "G-ST-RTP-Inventory-EL"
If Group = "140" Then Blaster = "G-ST-RTP-Inventory-PN"
If Group = "140" Then Blaster = "G-ST-RTP-Inventory-Metals"
If Group = "160N-317" Then Blaster = "G-ST-RTP-Inventory-Stability-1"
If Group = "160N-220" Then Blaster = "G-ST-RTP-Inventory-Stability-1"
If Group = "160N-318" Then Blaster = "G-ST-RTP-Inventory-Stability-2"
If Group = "160N-518" Then Blaster = "G-ST-RTP-Inventory-Stability-2"
If Group = "160N-218" Then Blaster = ""
If Group = "160S-230" Then Blaster = "G-ST-RTP-Inventory-Analytical"

What is the best way to handle changing this data? My recent idea is to create a text file or xml file and paste in the variables there. I would then call the *.txt or *.xml (or what is best) to have the code update those variables. Any suggestions? 

Private Sub Inquire()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim OneRow As Long
    Dim ChemicalName As String
    Dim LotNumber As String
    Dim Customer As String
    Dim ChoiceSelect As Integer
    Dim EmailBody As String
    Dim Blaster As String
    Dim Group As String
    Dim Quantity As String
    Dim RequestingGroup As String
    Dim header As Integer
    Dim ws As String
    Dim Storage As String
    Dim TeamName As String
            OneRow = ActiveCell.Row
            Group = Cells(OneRow, 2)
            Storage = Cells(OneRow, 3)
            Customer = Cells(OneRow, 4)
            ChemicalName = Cells(OneRow, 6)
            LotNumber = Cells(OneRow, 13)
    
    header = 1
 
    ws = ActiveSheet.Name
 
    If header = ActiveCell.Row Then
    MsgBox ("You are attempting to inquire about the header. There isn't more to really say.")

    ElseIf ActiveCell.Value = "" Then
    MsgBox ("You are attempting to inquire about an incomplete record. Please select a cell with the inventory contents and try the inquiry again.")

    ElseIf ws <> Sheets(4).Name Then
    MsgBox ("You are attempting to inquire about a chemical not in the 'Site View' sheet. Please select a chemical from the 'Site View' sheet and try again.")
    
    Else

    If Group = "160N-517" Then TeamName = "Biophysical Characterization and MS-S Inorganics"
    If Group = "175" Then TeamName = "QCCA"
    If Group = "120N" Then TeamName = "Bio"
    If Group = "160N-417" Then TeamName = "Extractable and Leachable"
    If Group = "140" Then TeamName = "Pulmonary and Nasal"
    If Group = "140" Then TeamName = "Metals"
    If Group = "160N-317" Then TeamName = "Stability"
    If Group = "160N-220" Then TeamName = "Stability"
    If Group = "160N-318" Then TeamName = "Stability"
    If Group = "160N-518" Then TeamName = "Stability"
    If Group = "160N-218" Then TeamName = "Product Venture Group"
    If Group = "160S-230" Then TeamName = "Sterile Analytical or Analytical Research and Development"

    If Group = "160N-517" Then Blaster = "G-ST-RTP-Inventory-BPC-MS-S"
    If Group = "175" Then Blaster = "G-ST-RTP-Inventory-QCCA"
    If Group = "120N" Then Blaster = "G-ST-RTP-Inventory-Bio"
    If Group = "160N-417" Then Blaster = "G-ST-RTP-Inventory-EL"
    If Group = "140" Then Blaster = "G-ST-RTP-Inventory-PN"
    If Group = "140" Then Blaster = "G-ST-RTP-Inventory-Metals"
    If Group = "160N-317" Then Blaster = "G-ST-RTP-Inventory-Stability-1"
    If Group = "160N-220" Then Blaster = "G-ST-RTP-Inventory-Stability-1"
    If Group = "160N-318" Then Blaster = "G-ST-RTP-Inventory-Stability-2"
    If Group = "160N-518" Then Blaster = "G-ST-RTP-Inventory-Stability-2"
    If Group = "160N-218" Then Blaster = ""
    If Group = "160S-230" Then Blaster = "G-ST-RTP-Inventory-Analytical"
    
    ChoiceSelect = MsgBox("Would you like to inquire about the " & ChemicalName & _
    ", lot " & LotNumber & ", from the " & TeamName & " group?" & vbNewLine & vbNewLine & _
    "The distribution list, " & Blaster & ", will be emailed to facilitate locating the chemical but it is" & _
    " your responsibility to confirm and obtain the " & ChemicalName & " from the " & Storage & " storage zone within building " & Group & ".", vbYesNo, "Chemical Inquiry")
   If ChoiceSelect = vbNo Then
   Exit Sub
   Else
       
    Quantity = InputBox("How much " & ChemicalName & _
    " do you require?", "Quantity of Chemical Request")
    
    RequestingGroup = InputBox("Which group are you with?", "Group Allocation")
    EmailBody = "Is it possible for the " & RequestingGroup & " group to borrow " & Quantity & _
    " of " & ChemicalName & ", lot " & LotNumber & ", from the " & Group & " group for our project?"

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Blaster
        .CC = ""
        .BCC = ""
        .Subject = "Chemical Inquiry of " & ChemicalName & " Lot, " & LotNumber
        .HTMLBody = EmailBody
        .Send   'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End If
    End If
End Sub