Have VBA Pull Value from Excel based on a drop down

[psulions83]

Hi,

I have a drop down in excel that I want the value in that box to run a certain macro in my access dB when I press a button. So I pick a value in my dropdown then click the button to kick off the macro. If there is a way that when you click a button you can be prompted with these drop down values and does the same thing I am good with that approach too. My code is below.

Sub accessMacro()


   Dim appAccess As Access.Application


   Set appAccess = New Access.Application
   
   'Debug.Print ThisWorkbook.Path & "\Access dB.accdb"
   
   appAccess.OpenCurrentDatabase ThisWorkbook.Path & "\Access dB.accdb"
   
   appAccess.Visible = False
   
   Application.DisplayAlerts = False
   
  [COLOR=#ff0000] appAccess.DoCmd.RunMacro "Mcr_All_"[/COLOR]     'Macro to run the table loads. Bascially in my dropdown if I had values I want this to be more dynamic.  All the Macros are named Mcr_All_abc
   
   ActiveWorkbook.refreshAll


   appAccess.CloseCurrentDatabase
     
   ActiveWorkbook.Activate
   
   MsgBox "Done", vbCritical
   
   ActiveWorkbook.refreshAll


End Sub