Command button code to include worksheets_SC

[Ram]

In this code my range Module_2(cell name), is in a different
worksheet(Path_Module). How do I declare it. This code is searching
for Module_2 on the same worksheet where the command button is
present. Hence i get error "Range not found".

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim Rng2 As Range


Set Rng = Me.Range("Module_2") '<<=== CHANGE


On Error Resume Next
Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If Not Rng2 Is Nothing Then
MsgBox Prompt:="All of the fields " _
& "Learning Path name, Availability date and
Domain" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Rng2.Cells(1).Select
Exit Sub
End If


Me.Next.Select
End Sub

Thanks a lot for help.

 

[Jim Thomlinson]

You can either reference the worksheet by tab name or directly by code name.

Tab name
Set Rng = sheets("My Tab Name").Range("Module_2")
Code name (if code name does not make sense just rply back...
Set Rng = Sheet1.Range("Module_2")

 

[Ram]

You can either reference the worksheet by tab name or directly by code name.

Tab name
Set Rng = sheets("My Tab Name").Range("Module_2")
Code name (if code name does not make sense just rply back...
Set Rng = Sheet1.Range("Module_2")
--
HTH...

Jim Thomlinson











- Show quoted text -

Hey Jim

When I declare it this way: Set Rng =
Me.Worksheets("Cert_Path_module").Range("Module_2"), I get Method or
data member not found error.

Pls help i'm pretty new to coding

Thanks!

 

[Ram]

Hey Jim

When I declare it this way: Set Rng =
Me.Worksheets("Cert_Path_module").Range("Module_2"), I get Method or
data member not found error.

Pls help i'm pretty new to coding

Thanks!- Hide quoted text -

- Show quoted text -

I also get error Run-time error '1004' Select method of Range class
failed. When the Rng2.Cells(1).Select
line is executed.

If Not Rng2 Is Nothing Then
MsgBox Prompt:="All of the fields " _
& "Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Rng2.Cells(1).Select<===============Error mentioned above

What changes do i have to make ?? pls help
thanks!