PDA

View Full Version : Solved: Getting 1004 error for below code



pmpinaki
12-21-2012, 05:42 AM
Hi All,

I have written the below function

Function printval(strVal As Variant) As Variant
Call prnt(strVal)
End Function
Sub prnt(strVal As Variant)
On Error GoTo exp
Sheets("Sheet3").Select
Sheet3.Activate
Sheet3.Visible = True
Sheet3.Cells(1, 4) = strVal
catch:
Exit Sub
exp:
MsgBox Err.Description 'getting error number 1004
Resume catch
End Sub

While executing I am getting error number 1004. Can any one help me to resolve this issue?

Aflatoon
12-21-2012, 05:57 AM
You can't activate a sheet before it is visible. (you don't need to make it visible or to activate it to assign a value to a cell though)

pmpinaki
12-21-2012, 06:02 AM
However if removed the activate and visible statement as below

Function printval(strVal As Variant) As Variant
Call prnt(strVal)
End Function
Sub prnt(strVal As Variant)
On Error GoTo exp
Sheet3.Cells(1, 4) = strVal
catch:
Exit Sub
exp:
MsgBox Err.Description 'getting error number 1004
Resume catch
End Sub

still I am getting the 1004 error. My idea is to get the data from the from any sheet and print it on only Sheet3 within the same workbook.
Any thought?

Aflatoon
12-21-2012, 06:08 AM
Is sheet3 protected?

pmpinaki
12-21-2012, 07:13 AM
No Sheet 3 is not protected

Aflatoon
12-21-2012, 07:25 AM
How are you calling PrintVal? (It won't work from a cell)

pmpinaki
12-21-2012, 10:13 PM
Well I am trying from cell... Since I have written another function to reverse a string and it works fine when I tried to call from cell. However there I am not copying the value to different sheets.
So can you please suggest me what would be the correct approach to resolve this issue?

Aussiebear
12-23-2012, 04:34 PM
Does the following work?

Function printval(strVal As Variant)
Call prnt(strVal)
End Function

Sub prnt(strVal)
On Error Goto exp
Sheet3.Cells(1, 4).Value = strVal
exp:
MsgBox Err.Description 'getting error number 1004
End Sub

pmpinaki
12-24-2012, 05:07 AM
Nope the above code does not work :(

mdmackillop
12-24-2012, 08:46 AM
Private Sub Worksheet_Change(ByVal Target As Range)
printval Target
End Sub

Function printval(strVal As Variant) As Variant
Sheets("Sheet3").Cells(1, 4) = strVal
End Function

pmpinaki
12-24-2012, 12:59 PM
Still I am getting error 1004 :(

Aussiebear
12-24-2012, 03:47 PM
We have the correct worksheet Name?

mdmackillop
12-25-2012, 06:24 AM
Can you post your workbook?

pmpinaki
12-25-2012, 10:37 AM
Attached the workbook for reference.

mdmackillop
12-25-2012, 05:47 PM
You cannot change another cell using an "in cell" function.
Try this instead.
In Sheet 1 module
Private Sub Worksheet_Change(ByVal Target As Range)
printval Target
End Sub
In Module 1
Function printval(strVal As Variant)
Sheets("Sheet3").Cells(1, 4) = strVal
End Function

pmpinaki
12-25-2012, 10:19 PM
It works :)
Thanks for your patience to understand my problem.