CODEWITHSUNDEEP
excelvba
Sangram Nandkhile
Sangram Nandkhile

Reputation: 18192

Check if a particular sheet is the activesheet

How to check if particular sheet is an active sheet or not?

I want particular functionality to be used for worksheet having name Data.

I can check if Data sheet exists or not using following code

Dim ws As Worksheet
Set ws = Wb.Sheets("Data")
If ws Is Nothing Then

Else

But how to check if Data sheet is an active sheet or not ? is there any thing like

If ws Is Activesheet Then

UPDATE:

I have added following code in the one of the Class module of addin.

What i am trying to do is to manage other excel sheets from this addin. I want to call procedure paste_cells if the the active sheet is having name "Data".

Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox "Activate"

Dim ws As Worksheet
Set ws = Wb.Sheets("Data")

If ws Is ActiveSheet Then  ' if active sheet is having name Data
App.OnKey "^v", Procedure:="Paste_cell" 'paste cell is procedure i want to add when active sheet is Data
Else
App.OnKey "^v"
End If

End Sub

Upvotes: 3

Views: 40337

Answers (3)

brettdj
brettdj

Reputation: 55692

you should

  1. use error handling as the sheet may not exist

  2. For an addin you would normally use ActiveWorkbook,ie

     Dim ws As Worksheet
 On Error Resume Next
 Set ws = ActiveWorkbook.Sheets("Data")
 On Error GoTo 0
 If ws Is Nothing Then
     MsgBox "Data sheet not found"
 Else
     If ws.Name = ActiveWorkbook.ActiveSheet.Name Then
         MsgBox "Data sheet found and is active"
     Else
         MsgBox "Data sheet found but is inactive"
     End If
 End If

Upvotes: 2

JMax
JMax

Reputation: 26601

You can also check objects (we never know if the user has opened a workbook where the sheet has the same name):

Sub test()
  On Error Resume Next
  If ActiveWorkbook.Worksheets("Data") Is ActiveSheet Then MsgBox ("ok")
  On Error GoTo 0
End Sub

See MSDN

Thanks to brettdj for the reminder about the error handling.

[EDIT] Within your code:

Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox "Activate"

Dim ws As Worksheet
On Error Resume Next
Set ws = Wb.Sheets("Data")
On Error GoTo 0

If Not ws Is Nothing and ws Is ActiveSheet Then  ' if active sheet is having name Data
  App.OnKey "^v", Procedure:="Paste_cell" 'paste cell is procedure i want to add when active sheet is Data
Else
  App.OnKey "^v"
End If
End Sub

Upvotes: 4

Jandrejc
Jandrejc

Reputation: 499

I would use: 

If Wb.ActiveSheet.Name = ws.Name Then

End If

Upvotes: 1

Related Questions