Command button error
 

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!

Command button error
 

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!

Command button error
 

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

Command button error
 

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

Command button error
 

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.

Command button error
 

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

Command button error
 

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

Command button error
 

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

Command button error
 

On Jun 27, 11:08 pm, Dave Peterson 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

Command button error
 

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

Command button error
 

On Jul 3, 10:29 pm, Dave Peterson wrote:
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 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- Hide quoted text -

- Show quoted text -

Hi Dave,

Hope this helps..

Module_2 is just a single cell. And it is in worksheet
"Cert_Path_module". This needs to be filled. If it is filled then on
clicking the command button next screen i.e "Cert_Details" should be
displayed. If its not filled, a Msg box should appear asking user to
fill it and on clicking ok Module_2 Cell should be activated. The
command button is in a worksheet called "LI".

Thanks & Regards
SRC

Command button error
 

You can also use:

if isempty(worksheets("Cert_Path_module").range("modu le_2).value) then
'do what you want if the cell is empty
else
'do what you want if the cell is filled in
end if

If that doesn't help, post what you tried.


Ram wrote:
<<snipped

Hi Dave,

Hope this helps..

Module_2 is just a single cell. And it is in worksheet
"Cert_Path_module". This needs to be filled. If it is filled then on
clicking the command button next screen i.e "Cert_Details" should be
displayed. If its not filled, a Msg box should appear asking user to
fill it and on clicking ok Module_2 Cell should be activated. The
command button is in a worksheet called "LI".

Thanks & Regards
SRC

--

Dave Peterson

Command button error
 

On Jul 4, 4:44 pm, Dave Peterson wrote:
You can also use:

if isempty(worksheets("Cert_Path_module").range("modu le_2).value) then
'do what you want if the cell is empty
else
'do what you want if the cell is filled in
end if

If that doesn't help, post what you tried.

Ram wrote:

<<snipped



Hi Dave,

Hope this helps..

Module_2 is just a single cell. And it is in worksheet
"Cert_Path_module". This needs to be filled. If it is filled then on
clicking the command button next screen i.e "Cert_Details" should be
displayed. If its not filled, a Msg box should appear asking user to
fill it and on clicking ok Module_2 Cell should be activated. The
command button is in a worksheet called "LI".

Thanks & Regards
SRC

--

Dave Peterson

Hi Dave,

This is the code i have now.

Private Sub CommandButton1_Click()
If IsEmpty(Worksheets("Cert_Path_module").Range("modu le_2").Value)
Then
MsgBox Prompt:="All the fields " _
& "for Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Worksheets("Cert_Path_module").Range("module_2").A ctivate
Else
Worksheets("Cert_Details").Range("LPP1").Select
End If
End Sub

The code is working fine a msg box is displayed if Module_2 is blank,
and does not appear if Module_2 has a value.

However when i leave Module_2 blank, and when i click on "OK" in the
msg box that appears as per the code, I get " Run - time error
'1004':
Application-defined or object defined error.

And when i fill a value in Module_2 i directly get the above error.

This means the system is unable to execute
Worksheets("Cert_Path_module").Range("module_2").A ctivate
and Worksheets("Cert_Details").Range("LPP1").Select

where am i going wrong here?

Thanks a lot for your help.

Regards,
SRC

Command button error
 

This line is the problem:
Worksheets("Cert_Details").Range("LPP1").Select

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

One way to fix it is:
application.goto Worksheets("Cert_Details").Range("LPP1"), scroll:=true

another way is to select the sheet, then select the range:

with Worksheets("Cert_Details")
.select
.Range("LPP1").Select
end with


Ram wrote:

On Jul 4, 4:44 pm, Dave Peterson wrote:
You can also use:

if isempty(worksheets("Cert_Path_module").range("modu le_2).value) then
'do what you want if the cell is empty
else
'do what you want if the cell is filled in
end if

If that doesn't help, post what you tried.

Ram wrote:

<<snipped



Hi Dave,

Hope this helps..

Module_2 is just a single cell. And it is in worksheet
"Cert_Path_module". This needs to be filled. If it is filled then on
clicking the command button next screen i.e "Cert_Details" should be
displayed. If its not filled, a Msg box should appear asking user to
fill it and on clicking ok Module_2 Cell should be activated. The
command button is in a worksheet called "LI".

Thanks & Regards
SRC

--

Dave Peterson

Hi Dave,

This is the code i have now.

Private Sub CommandButton1_Click()
If IsEmpty(Worksheets("Cert_Path_module").Range("modu le_2").Value)
Then
MsgBox Prompt:="All the fields " _
& "for Module 2 information" _
& " should be filled.", _
Buttons:=vbInformation, _
Title:="Missing Data"
Worksheets("Cert_Path_module").Range("module_2").A ctivate
Else
Worksheets("Cert_Details").Range("LPP1").Select
End If
End Sub

The code is working fine a msg box is displayed if Module_2 is blank,
and does not appear if Module_2 has a value.

However when i leave Module_2 blank, and when i click on "OK" in the
msg box that appears as per the code, I get " Run - time error
'1004':
Application-defined or object defined error.

And when i fill a value in Module_2 i directly get the above error.

This means the system is unable to execute
Worksheets("Cert_Path_module").Range("module_2").A ctivate
and Worksheets("Cert_Details").Range("LPP1").Select

where am i going wrong here?

Thanks a lot for your help.

Regards,
SRC

--

Dave Peterson