Vba - Macro Action based on cell value

[jamesmev]

I currently have a data validation list drop down that once the value changes it actions a Macro.
The issue is that I have 56 variables and I feel like writing 56 separate macros seems too much I must be missing something.

The VBA I am currently using is as below.. I am repeating this 56 times for each instance and change based on H5.


Private Sub Worksheet_Change
If Target = Range("H5") Then
If InStr(1, Range("H5"), "Customer info") > 0 Then CustInfo
End If
End Sub


Any suggestions or better way to run this?

 

[Fluff]

A few questions.
1) What cell(s) is/are the DV in?
2) what are the possible values in the DV?
3) What do you want to happen when one is selected?

 

[jamesmev]

A few questions.
1) What cell(s) is/are the DV in?
2) what are the possible values in the DV?
3) What do you want to happen when one is selected?

1. Cells in the DV are an indirect index/match from the data worksheet.
2. The values are all text
3. The results are all either document links online/server or email addresses.

What are the thoughts on doing a hyperlinked index match and running a single macro to that cell?

 

[Fluff]

Ok, lets try that again.

1) What is the location of the DV that you want this to work on.
2) Can you give some examples of the values that are in the DV.
3) With the examples given in point 2, what does the macro need to do.

 

[jamesmev]

Ok, lets try that again.

1) What is the location of the DV that you want this to work on.
Sheet 1! J11

2) Can you give some examples of the values that are in the DV.

[TABLE="width: 170"]
<tbody>[TR]
[TD]Contract Inquiry (email)[/TD]
[/TR]
[TR]
[TD]Rebates (document)[/TD]
[/TR]
[TR]
[TD]Credit Investigations (email)[/TD]
[/TR]
[TR]
[TD]Equipment Settlement Services (document)
[/TD]
[/TR]
</tbody>[/TABLE]
3) With the examples given in point 2, what does the macro need to do
The macro needs to open the file or start an email with the correct address.

 

[Fluff]

Ok, do you have a list anywhere that shows what document to open, or which e-mail address to use?

 

[jamesmev]

Ok, do you have a list anywhere that shows what document to open, or which e-mail address to use?

Yes. I have it sourced on a different tab.

 

[Fluff]

In that case you can use something like
In the sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim copyto As String
   Dim docs As String

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "J11" Then
      If InStr(1, Target.Value, "email", vbTextCompare) > 0 Then
         copyto = Application.WorksheetFunction.vlookup(Left(Target.Value, InStr(1, Target.Value, "(") - 2), [COLOR=#ff0000]Sheets("Sheet2").Range("A2:B6")[/COLOR], 2, 0)
         Call myemail(copyto)
      Else
         docs = Application.WorksheetFunction.vlookup(Left(Target.Value, InStr(1, Target.Value, "(") - 2), [COLOR=#ff0000]Sheets("Sheet2").Range("A2:B6")[/COLOR], 2, 0)
         Call mydocs(docs)
      End If
   End If
End Sub

Change parts in red to suit.
And then in a regular module the macros you want to call

Sub myemail(copyto As String)
MsgBox copyto
End Sub
Sub mydocs(docs As String)
MsgBox docs
End Sub