CODEWITHSUNDEEP
vbaexcel
Sean Bailey
Sean Bailey

Reputation: 375

Worksheet Change Event

I have the below code which I effectively want to rename worksheets based on the Value of I16. However if the target address is blank/ Nothing I wish to exit the sub. (this part of the code is not working).

If anyone could advise how I could resolve this issue it would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("I16")
Dim WSname As String
WSname = Range("I16").Value

If KeyCells Is Nothing Then Exit Sub

Sheet23.Name = "BISSB"
Sheet25.Name = "RMIB"
Sheet26.Name = "MORIB"
Worksheets(WSname).Name = "Stage 3 V1"

End Sub

Upvotes: 1

Views: 560

Answers (4)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

I think the correct way to use Change Event code is to tell the code when to be automatically triggered and perform some actions.

Right now, your code will be triggered and perform the actions defined in the code each time when any cell on the sheet gets changed.

I assume, you want to trigger the Change Event Code and perform some predefined actions only when the cell I16 gets changed and then rename the sheets as per the code. Right?

If so, you may try something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim KeyCells As Range
Set KeyCells = Range("I16")
Dim WSname As String
WSname = Range("I16").Value

If Not Intersect(Target, KeyCells) Is Nothing Then
    If Target <> "" Then
        Sheet23.Name = "BISSB"
        Sheet25.Name = "RMIB"
        Sheet26.Name = "MORIB"
        Worksheets(WSname).Name = "Stage 3 V1"
    End If
End If
End Sub

Upvotes: 0

vlad.lisnyi
vlad.lisnyi

Reputation: 345

You are already declare and set KeyCells to "I16". This is why if condition doesnt work - because KeyCells already contains cell. Ask if WSname = "" or check other way if it contains value or no.

Upvotes: 0

Mrig
Mrig

Reputation: 11727

Instead of 

If KeyCells Is Nothing Then Exit Sub

use

If IsEmpty(KeyCells) Then Exit Sub

The ISEMPTY function can be used to check for blank cells. If cell is blank it will return TRUE else FALSE.

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33662

Replace:

If KeyCells Is Nothing Then Exit Sub

With:

If Trim(WSname) = "" Then Exit Sub

Explanation: you already use Set KeyCells = Range("I16") in your code, so you set your KeyCells Range, therefore it will never be Nothing. You want to check the value of KeyCells range, and you have your WSname String variable.

Upvotes: 2

Related Questions