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.

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


"Ram" wrote:

>
> 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.
>
>

On Jun 25, 11:01 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> 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
>
>
>
> "Ram" wrote:
>
> > 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.- Hide quoted text -
>
> - 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!

On Jun 27, 1:55 pm, Ram <sreeram....@gmail.com> wrote:
> On Jun 25, 11:01 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
>
>
>
>
>
> This-.com> wrote:
> > 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
>
> > "Ram" wrote:
>
> > > 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.- Hide quoted text -
>
> > - 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!- 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!