Hi

I get the following error when i execute this code

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


Set Rng = Worksheets("Cert_Path_module").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 " _
& "Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Rng2.Cells(1).Select <===============Error msg "Run-time
error '1004' Application-defined or object-defined error"
Exit Sub


End If


Me.Next.Select

End Sub

What modifications should me made in this code??

Thanks!

Can you explain what you want. cells(1) isn't needed. You can do just

Rng2.Select - select entire range

or

Cells(Rng2.Row, Rng2.Column).Select - select first cell of Rng2

"Ram" wrote:

> Hi
>
> I get the following error when i execute this code
>
> Private Sub CommandButton1_Click()
> Dim Rng As Range
> Dim Rng2 As Range
>
>
> Set Rng = Worksheets("Cert_Path_module").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 " _
> & "Module 2 information" _
> & " should be filled.", _
> Buttons:=vbInformation, _
> Title:="Missing Data"
> Rng2.Cells(1).Select <===============Error msg "Run-time
> error '1004' Application-defined or object-defined error"
> Exit Sub
>
>
> End If
>
>
> Me.Next.Select
>
> End Sub
>
> What modifications should me made in this code??
>
> Thanks!
>
>

If that commandbutton isn't on the Cert_Path_module worksheet, then you're going
to have some trouble.

You can only select a range on a worksheet that's active.

You can change it this way:
'select the sheet
Rng2.parent.select
'select the range
Rng2.Cells(1).Select

or
application.goto Rng2.Cells(1), scroll:=true




Ram wrote:
>
> Hi
>
> I get the following error when i execute this code
>
> Private Sub CommandButton1_Click()
> Dim Rng As Range
> Dim Rng2 As Range
>
> Set Rng = Worksheets("Cert_Path_module").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 " _
> & "Module 2 information" _
> & " should be filled.", _
> Buttons:=vbInformation, _
> Title:="Missing Data"
> Rng2.Cells(1).Select <===============Error msg "Run-time
> error '1004' Application-defined or object-defined error"
> Exit Sub
>
> End If
>
> Me.Next.Select
>
> End Sub
>
> What modifications should me made in this code??
>
> Thanks!

--

Dave Peterson

On Jun 27, 3:38 pm, Joel <J...@discussions.microsoft.com> wrote:
> Can you explain what you want. cells(1) isn't needed. You can do just
>
> Rng2.Select - select entire range
>
> or
>
> Cells(Rng2.Row, Rng2.Column).Select - select first cell of Rng2
>
>
>
> "Ram" wrote:
> > Hi
>
> > I get the following error when i execute this code
>
> > Private Sub CommandButton1_Click()
> > Dim Rng As Range
> > Dim Rng2 As Range
>
> > Set Rng = Worksheets("Cert_Path_module").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 " _
> > & "Module 2 information" _
> > & " should be filled.", _
> > Buttons:=vbInformation, _
> > Title:="Missing Data"
> > Rng2.Cells(1).Select <===============Error msg "Run-time
> > error '1004' Application-defined or object-defined error"
> > Exit Sub
>
> > End If
>
> > Me.Next.Select
>
> > End Sub
>
> > What modifications should me made in this code??
>
> > Thanks!- Hide quoted text -
>
> - Show quoted text -

Hi Joel,

Hi Joel,

This is my form design:-

I have a worksheet (Cert_Path_module), in which I have a field "No of
Modules". This field takes values from 1 to 5 in a drop down (Data ->
Validation). This field totally depends on entries by end user. They
can have any no. of modules max up to 5.

For e.g. if a user selects 2 from the drop down a set of rows get
unhidden and the user fills up details for 2 modules. For each of the
module info (1to5) I have a link to enter more info regarding the
module. On clicking this link it takes the user to "Learning Item1 so
on till Learning Item 5" (Seperate work sheet names) this depends on
which module user is in.

In each of the Learning Item worksheet (1to5) I have a continue button
in each sheet. Now the code should not take them to Work Sheet
"Cert_Details" (which is the next sheet to be displayed) unless all
the details are provided.

And on clicking this button I want a msg box to appear asking user to
fill module no 2
Information(only if user has not filled) and activate "Module_2" cell
in Cert_Path_module worksheet. If user has filled in all details in
module 2 page then it should take the user to Cert_Details work
sheet.

Note: Modules 1 to 5 depends on users. If user selects 2 then only
info for 2 modules has to be entered. (I have code for this).

Sorry for dragging this long. I hope you have understood. Thanks for
your help

Regards,
SRC

On Jun 27, 5:01 pm, Ram <sreeram....@gmail.com> wrote:
> On Jun 27, 3:38 pm, Joel <J...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > Can you explain what you want. cells(1) isn't needed. You can do just
>
> > Rng2.Select - select entire range
>
> > or
>
> > Cells(Rng2.Row, Rng2.Column).Select - select first cell of Rng2
>
> > "Ram" wrote:
> > > Hi
>
> > > I get the following error when i execute this code
>
> > > Private Sub CommandButton1_Click()
> > > Dim Rng As Range
> > > Dim Rng2 As Range
>
> > > Set Rng = Worksheets("Cert_Path_module").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 " _
> > > & "Module 2 information" _
> > > & " should be filled.", _
> > > Buttons:=vbInformation, _
> > > Title:="Missing Data"
> > > Rng2.Cells(1).Select <===============Error msg "Run-time
> > > error '1004' Application-defined or object-defined error"
> > > Exit Sub
>
> > > End If
>
> > > Me.Next.Select
>
> > > End Sub
>
> > > What modifications should me made in this code??
>
> > > Thanks!- Hide quoted text -
>
> > - Show quoted text -
>
> Hi Joel,
>
> Hi Joel,
>
> This is my form design:-
>
> I have a worksheet (Cert_Path_module), in which I have a field "No of
> Modules". This field takes values from 1 to 5 in a drop down (Data ->
> Validation). This field totally depends on entries by end user. They
> can have any no. of modules max up to 5.
>
> For e.g. if a user selects 2 from the drop down a set of rows get
> unhidden and the user fills up details for 2 modules. For each of the
> module info (1to5) I have a link to enter more info regarding the
> module. On clicking this link it takes the user to "Learning Item1 so
> on till Learning Item 5" (Seperate work sheet names) this depends on
> which module user is in.
>
> In each of the Learning Item worksheet (1to5) I have a continue button
> in each sheet. Now the code should not take them to Work Sheet
> "Cert_Details" (which is the next sheet to be displayed) unless all
> the details are provided.
>
> And on clicking this button I want a msg box to appear asking user to
> fill module no 2
> Information(only if user has not filled) and activate "Module_2" cell
> in Cert_Path_module worksheet. If user has filled in all details in
> module 2 page then it should take the user to Cert_Details work
> sheet.
>
> Note: Modules 1 to 5 depends on users. If user selects 2 then only
> info for 2 modules has to be entered. (I have code for this).
>
> Sorry for dragging this long. I hope you have understood. Thanks for
> your help
>
> Regards,
> SRC- Hide quoted text -
>
> - Show quoted text -

Hi Dave,

Your code modification works fine but, even if the cell "Module_2" is
not blank its not proceeding to next page. The msgbox pops up and
activates Module_2 cell. This should not be the case. If I have value
in module_2 cell i do not want the msgbox to pop up and activate
module_2 on clicking OK in the msgbx, i want it to display
"Cert_Details" worksheet.

Thanks for your help.

If you replaced your line of code that did the select with one of those
suggestions, I don't see how it could be going to that range.

Maybe you put the line of code in the wrong spot????

If you can't get it working, try posting your current code. And include the
name of the worksheet that owns the code, too. It'll make it less ambiguous (to
me anyway).

And add

Ram wrote:
>
> On Jun 27, 5:01 pm, Ram <sreeram....@gmail.com> wrote:
> > On Jun 27, 3:38 pm, Joel <J...@discussions.microsoft.com> wrote:
> >
> >
> >
> >
> >
> > > Can you explain what you want. cells(1) isn't needed. You can do just
> >
> > > Rng2.Select - select entire range
> >
> > > or
> >
> > > Cells(Rng2.Row, Rng2.Column).Select - select first cell of Rng2
> >
> > > "Ram" wrote:
> > > > Hi
> >
> > > > I get the following error when i execute this code
> >
> > > > Private Sub CommandButton1_Click()
> > > > Dim Rng As Range
> > > > Dim Rng2 As Range
> >
> > > > Set Rng = Worksheets("Cert_Path_module").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 " _
> > > > & "Module 2 information" _
> > > > & " should be filled.", _
> > > > Buttons:=vbInformation, _
> > > > Title:="Missing Data"
> > > > Rng2.Cells(1).Select <===============Error msg "Run-time
> > > > error '1004' Application-defined or object-defined error"
> > > > Exit Sub
> >
> > > > End If
> >
> > > > Me.Next.Select
> >
> > > > End Sub
> >
> > > > What modifications should me made in this code??
> >
> > > > Thanks!- Hide quoted text -
> >
> > > - Show quoted text -
> >
> > Hi Joel,
> >
> > Hi Joel,
> >
> > This is my form design:-
> >
> > I have a worksheet (Cert_Path_module), in which I have a field "No of
> > Modules". This field takes values from 1 to 5 in a drop down (Data ->
> > Validation). This field totally depends on entries by end user. They
> > can have any no. of modules max up to 5.
> >
> > For e.g. if a user selects 2 from the drop down a set of rows get
> > unhidden and the user fills up details for 2 modules. For each of the
> > module info (1to5) I have a link to enter more info regarding the
> > module. On clicking this link it takes the user to "Learning Item1 so
> > on till Learning Item 5" (Seperate work sheet names) this depends on
> > which module user is in.
> >
> > In each of the Learning Item worksheet (1to5) I have a continue button
> > in each sheet. Now the code should not take them to Work Sheet
> > "Cert_Details" (which is the next sheet to be displayed) unless all
> > the details are provided.
> >
> > And on clicking this button I want a msg box to appear asking user to
> > fill module no 2
> > Information(only if user has not filled) and activate "Module_2" cell
> > in Cert_Path_module worksheet. If user has filled in all details in
> > module 2 page then it should take the user to Cert_Details work
> > sheet.
> >
> > Note: Modules 1 to 5 depends on users. If user selects 2 then only
> > info for 2 modules has to be entered. (I have code for this).
> >
> > Sorry for dragging this long. I hope you have understood. Thanks for
> > your help
> >
> > Regards,
> > SRC- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Dave,
>
> Your code modification works fine but, even if the cell "Module_2" is
> not blank its not proceeding to next page. The msgbox pops up and
> activates Module_2 cell. This should not be the case. If I have value
> in module_2 cell i do not want the msgbox to pop up and activate
> module_2 on clicking OK in the msgbx, i want it to display
> "Cert_Details" worksheet.
>
> Thanks for your help.

--

Dave Peterson

On Jun 27, 7:03 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> If you replaced your line of code that did the select with one of those
> suggestions, I don't see how it could be going to that range.
>
> Maybe you put the line of code in the wrong spot????
>
> If you can't get it working, try posting your current code. And include the
> name of the worksheet that owns the code, too. It'll make it less ambiguous (to
> me anyway).
>
> And add
>
>
>
>
>
> Ram wrote:
>
> > On Jun 27, 5:01 pm, Ram <sreeram....@gmail.com> wrote:
> > > On Jun 27, 3:38 pm, Joel <J...@discussions.microsoft.com> wrote:
>
> > > > Can you explain what you want. cells(1) isn't needed. You can do just
>
> > > > Rng2.Select - select entire range
>
> > > > or
>
> > > > Cells(Rng2.Row, Rng2.Column).Select - select first cell of Rng2
>
> > > > "Ram" wrote:
> > > > > Hi
>
> > > > > I get the following error when i execute this code
>
> > > > > Private Sub CommandButton1_Click()
> > > > > Dim Rng As Range
> > > > > Dim Rng2 As Range
>
> > > > > Set Rng = Worksheets("Cert_Path_module").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 " _
> > > > > & "Module 2 information" _
> > > > > & " should be filled.", _
> > > > > Buttons:=vbInformation, _
> > > > > Title:="Missing Data"
> > > > > Rng2.Cells(1).Select <===============Error msg "Run-time
> > > > > error '1004' Application-defined or object-defined error"
> > > > > Exit Sub
>
> > > > > End If
>
> > > > > Me.Next.Select
>
> > > > > End Sub
>
> > > > > What modifications should me made in this code??
>
> > > > > Thanks!- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Hi Joel,
>
> > > Hi Joel,
>
> > > This is my form design:-
>
> > > I have a worksheet (Cert_Path_module), in which I have a field "No of
> > > Modules". This field takes values from 1 to 5 in a drop down (Data ->
> > > Validation). This field totally depends on entries by end user. They
> > > can have any no. of modules max up to 5.
>
> > > For e.g. if a user selects 2 from the drop down a set of rows get
> > > unhidden and the user fills up details for 2 modules. For each of the
> > > module info (1to5) I have a link to enter more info regarding the
> > > module. On clicking this link it takes the user to "Learning Item1 so
> > > on till Learning Item 5" (Seperate work sheet names) this depends on
> > > which module user is in.
>
> > > In each of the Learning Item worksheet (1to5) I have a continue button
> > > in each sheet. Now the code should not take them to Work Sheet
> > > "Cert_Details" (which is the next sheet to be displayed) unless all
> > > the details are provided.
>
> > > And on clicking this button I want a msg box to appear asking user to
> > > fill module no 2
> > > Information(only if user has not filled) and activate "Module_2" cell
> > > in Cert_Path_module worksheet. If user has filled in all details in
> > > module 2 page then it should take the user to Cert_Details work
> > > sheet.
>
> > > Note: Modules 1 to 5 depends on users. If user selects 2 then only
> > > info for 2 modules has to be entered. (I have code for this).
>
> > > Sorry for dragging this long. I hope you have understood. Thanks for
> > > your help
>
> > > Regards,
> > > SRC- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi Dave,
>
> > Your code modification works fine but, even if the cell "Module_2" is
> > not blank its not proceeding to next page. The msgbox pops up and
> > activates Module_2 cell. This should not be the case. If I have value
> > in module_2 cell i do not want the msgbox to pop up and activate
> > module_2 on clicking OK in the msgbx, i want it to display
> > "Cert_Details" worksheet.
>
> > Thanks for your help.
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Hi Dave,

I know it is very ambiguous.

this is the code i have:-

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

Set Rng = Worksheets("Cert_Path_module").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 " _
& "Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Rng2.Cells(24, 5).Select <===============Error msg "Run-time
error '1004' Application-defined or object-defined error"
Exit Sub

End If

Me.Next.Select

End Sub

You didn't paste your new code--you pasted the original code.

And I didn't see where you shared the name of the worksheet that held the code.

Did you try the earlier suggestion?

Ram wrote:
>
> > - Show quoted text -
>
> Hi Dave,
>
> I know it is very ambiguous.
>
> this is the code i have:-
>
> Private Sub CommandButton1_Click()
> Dim Rng As Range
> Dim Rng2 As Range
>
> Set Rng = Worksheets("Cert_Path_module").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 " _
> & "Module 2 information" _
> & " should be filled.", _
> Buttons:=vbInformation, _
> Title:="Missing Data"
> Rng2.Cells(24, 5).Select <===============Error msg "Run-time
> error '1004' Application-defined or object-defined error"
> Exit Sub
>
> End If
>
> Me.Next.Select
>
> End Sub

--

Dave Peterson

On Jun 27, 11:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You didn't paste your new code--you pasted the original code.
>
> And I didn't see where you shared the name of the worksheet that held the code.
>
> Did you try the earlier suggestion?
>
>
>
>
>
> Ram wrote:
>
> > > - Show quoted text -
>
> > Hi Dave,
>
> > I know it is very ambiguous.
>
> > this is the code i have:-
>
> > Private Sub CommandButton1_Click()
> > Dim Rng As Range
> > Dim Rng2 As Range
>
> > Set Rng = Worksheets("Cert_Path_module").Range("Module_2") '<<===
> > CHANGE
>
> > OnErrorResume Next
> > Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks)
> > OnErrorGoTo 0
>
> > 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(24, 5).Select <===============Errormsg "Run-time
> > error'1004' Application-defined or object-definederror"
> > Exit Sub
>
> > End If
>
> > Me.Next.Select
>
> > End Sub
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Hi Dave

This my code :

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


Set Rng = Worksheets("Cert_Path_module").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 the fields " _
& "for Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
' Rng2.Worksheets ("Cert_Path_module")
Application.Goto Rng2.Cells(24, 5), Scroll:=False

Else
Worksheets("Cert_Details").Select


End If
End Sub

This code is almost as how I want it to work but not quiet. Now if
there is a value in Module_2 cell the msg box should not come it
should take the learner to the next page "Cert_Details".

Thanks a lot for your help

I'm confused about how big the Module_2 range is.

Is it one cell and you want it filled?
Or is it multiple cells and you want all the cells filled?
Or is it multiple cells and you want at least one cell filled?

If all the cells (no matter how many--one cell or lots of cells) have to be
filled.

if rng.cells.count = application.counta(rng) then
'all filled in
else
'at least one empty cell
end if

If at least one cell has to be filled in:

if application.counta(rng) > 0 then
'at least one filled in
else
'all are empty
end if










Ram wrote:
>
> On Jun 27, 11:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > You didn't paste your new code--you pasted the original code.
> >
> > And I didn't see where you shared the name of the worksheet that held the code.
> >
> > Did you try the earlier suggestion?
> >
> >
> >
> >
> >
> > Ram wrote:
> >
> > > > - Show quoted text -
> >
> > > Hi Dave,
> >
> > > I know it is very ambiguous.
> >
> > > this is the code i have:-
> >
> > > Private Sub CommandButton1_Click()
> > > Dim Rng As Range
> > > Dim Rng2 As Range
> >
> > > Set Rng = Worksheets("Cert_Path_module").Range("Module_2") '<<===
> > > CHANGE
> >
> > > OnErrorResume Next
> > > Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks)
> > > OnErrorGoTo 0
> >
> > > 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(24, 5).Select <===============Errormsg "Run-time
> > > error'1004' Application-defined or object-definederror"
> > > Exit Sub
> >
> > > End If
> >
> > > Me.Next.Select
> >
> > > End Sub
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Dave
>
> This my code :
>
> Private Sub CommandButton1_Click()
> Dim Rng As Range
> Dim Rng2 As Range
>
> Set Rng = Worksheets("Cert_Path_module").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 the fields " _
> & "for Module 2 information" _
> & " should be filled.", _
> Buttons:=vbInformation, _
> Title:="Missing Data"
> ' Rng2.Worksheets ("Cert_Path_module")
> Application.Goto Rng2.Cells(24, 5), Scroll:=False
>
> Else
> Worksheets("Cert_Details").Select
>
> End If
> End Sub
>
> This code is almost as how I want it to work but not quiet. Now if
> there is a value in Module_2 cell the msg box should not come it
> should take the learner to the next page "Cert_Details".
>
> Thanks a lot for your help

--

Dave Peterson