Hi All,
I have written the below function
[VBA]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[/VBA]
While executing I am getting error number 1004. Can any one help me to resolve this issue?
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)
Be as you wish to seem
However if removed the activate and visible statement as below
[VBA]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
[/VBA]
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?
Is sheet3 protected?
Be as you wish to seem
No Sheet 3 is not protected
How are you calling PrintVal? (It won't work from a cell)
Be as you wish to seem
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?
Does the following work?
[VBA]
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
[/VBA]
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Nope the above code does not work
[VBA]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[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Still I am getting error 1004
We have the correct worksheet Name?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Can you post your workbook?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Attached the workbook for reference.
You cannot change another cell using an "in cell" function.
Try this instead.
In Sheet 1 module
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
printval Target
End Sub
[/VBA]In Module 1
[VBA]Function printval(strVal As Variant)
Sheets("Sheet3").Cells(1, 4) = strVal
End Function[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
It works
Thanks for your patience to understand my problem.
Posting Permissions
Reply With Quote
