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!

On Jun 27, 3:38 pm, Joel 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 wrote:
On Jun 27, 3:38 pm, Joel 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 wrote:
On Jun 27, 3:38 pm, Joel 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 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 wrote:
On Jun 27, 3:38 pm, Joel 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

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