Reduce duplicates vba code with variables

james potter

New Member
Joined
Aug 15, 2005
Messages
45
Hello,

I have made a vba code which will run a macro when a certain cell is being modify. I have duplicates this code for several cell in the sheet. In the code only the absolute reference of the cell change. Is there a way to make those reference variable so that there less code necessary?

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     If Target.Address = "$K$7" Then
          Application.EnableEvents = False
          Target = Now
          Application.EnableEvents = True
     Call report_b7


If Target.Address = "$M$7" Then
          Application.EnableEvents = False
          Target = Now
          Application.EnableEvents = True
     Call cobras_b7
End If


Sub cobras_b7()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
    .To = Format(Range("$J$7"))
    .Subject = "Cobras Charts " & Format(Range("$B$7"))
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _
    & Chr(13) & "text"
    .Display
    SendKeys "^{end}"
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub


Sub report_b7()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
    .To = Format(Range("$J$7"))
    .Subject = "text " & Format(Range("$B$7"))
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _
    " text" & Chr(13) & Chr(13) & Chr(13) & "text" _
    & Chr(13) & "text"
    .Display
    SendKeys "^{end}"
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

james potter

New Member
Joined
Aug 15, 2005
Messages
45
I see it too. I was not making meself clear.

I would like to make the cell reference variable. I have make the cell references bold in the code below. I think the first two are difficult to make variable, but the subs report & cobras can be made variable if I able to put the cellnumber/row in the memory of the code. How can I do that?

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
     If Target.Address = "$K$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call report_b7 


If Target.Address = "$M$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call cobras_b7 
End If 


Sub cobras_b7() 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Range("$J$7")) 
    .Subject = "Cobras Charts " & Format(Range("$B$7")) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _ 
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "text" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub 


Sub report_b7() 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Range("$J$7")) 
    .Subject = "text " & Format(Range("$B$7")) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _ 
    " text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "text" 
    .Display 
    SendKeys "^{end}"
 

james potter

New Member
Joined
Aug 15, 2005
Messages
45
please help make my vba code shorter

I'll make another attempt to make myself clear.

First what will my vba-code do for me. It generates a email message where certains fields are filled with value of cells in my worksheet. This macro is tiggered by changes in a certain cell.

This is the vba-code to trigger to call the macro.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     If Target.Address = "$K$7" Then
          Application.EnableEvents = False
          Target = Now
          Application.EnableEvents = True
     Call report_b7

This next part is the actual vba-code of sub report_b7
Code:
Sub report_b7()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
    .To = Format(Range("$J$7"))
    .Subject = "text" & Format(Range("$B$7"))
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _
    & Chr(13) & "Afdeling Reporting"
    .Display
    SendKeys "^{end}"
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

I would like to make this second part vba-code cell reference independent. As you can see, is every cell reference in the same row. So I think there is a way to put the cell reference of the first vba-code (macro trigger) in memory, like a name. Then in the second part vba-code I can refer to this name in combination with offset.

I think it's possible to do, but I do not know how. I have tried but failed.

Any help is appriciate
 

Forum statistics

Threads
1,140,916
Messages
5,703,161
Members
421,278
Latest member
16cwilliams

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top