More names for my VBA code

**Leeboy** · 03-19-2010, 04:27 AM

Hi

My code is almost working now ^^
Now my problem is that i need more names in my code, and i'm unsure where i can change.

Code:

 Private Sub Worksheet1_Change(ByVal Target As Range)
Dim total As Integer
total = Application.Sum(Sheets(1).Range("A14:I14"))

If total = 227 Then
Application.GoTo Sheets(1).Range("A17"), True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim total As Integer
total = Application.Sum(Sheets(1).Range("A15:I15"))

If total = 179 Then
Application.GoTo Sheets(1).Range("A17"), True
End If
End Sub

it is this, excel says I shall change the name of.

Code:

 Private Sub Worksheet_Change(ByVal Target As Range)

As you can see in my first code i use

Code:

Worksheet_Change 
and 
Worksheet1_Change

But when i use 2, it wont work. Is there another way to change to name so i can keep adding more codes like this?

**JeanRage** · 03-19-2010, 04:32 AM

Hi,

No ... Event Macros cannot be replicated ...
You have to make the effort to code all your requests into a single event module ...
You need to adjust the ranges and use If or Select Case for many instances ...

HTH

**Leeboy** · 03-19-2010, 04:50 AM

Aww.. Would have been so easy if it was possible -.- oh well. Going to write some codes

Thanks

**JBeaucaire** · 03-19-2010, 12:22 PM

Construct a single ws_change macro and organize the evaluation of the total variable into a select case statement...like so:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Total As Long

Total = Application.Sum(Sheets(1).Range("A15:I15"))

    Select Case Total
        Case 227:   Application.GoTo Sheets(1).Range("A17"), True
        Case 179:   Application.GoTo Sheets(1).Range("A17"), True
    End Select

End Sub

**royUK** · 03-19-2010, 03:58 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Try using the correct terms, Names are not Events

Thread: More names for my VBA code

Thread Tools

Display

More names for my VBA code

Re: More names for my VBA code

Re: More names for my VBA code

Re: More names for my VBA code

Re: More names for my VBA code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions