Command button error

R

Ram

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

Joel

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
 
D

Dave Peterson

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
 
R

Ram

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













- Show quoted text -
Click to expand...

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
 
R

Ram

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 -
Click to expand...

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

Dave Peterson

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
 
R

Ram

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









--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
D

Dave Peterson

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

Ram

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?














--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
D

Dave Peterson

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
 
R

Ram

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















--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
D

Dave Peterson

You can also use:

if isempty(worksheets("Cert_Path_module").range("module_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:
 
R

Ram

You can also use:

if isempty(worksheets("Cert_Path_module").range("module_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>>
Click to expand...

Hi Dave,

This is the code i have now.

Private Sub CommandButton1_Click()
If IsEmpty(Worksheets("Cert_Path_module").Range("module_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").Activate
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").Activate
and Worksheets("Cert_Details").Range("LPP1").Select

where am i going wrong here?

Thanks a lot for your help.

Regards,
SRC
 
D

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Command button code to include worksheets_SC 3
Highlight a cell after update in a specific range 2
Match style 0
Extract Text in braquet and list of terms from 2 Docs 3
End process at last row of data 3
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Scrol window 1
Working outlook via vba code with xlsx - error 0

Top