Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How to hide/show an embedded Word doc in Excel
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
How to hide/show an embedded Word doc in Excel
Resolved · Medium Priority · Version 2003
Reply Fri 18 Feb 2011, 13:26Delegate Carole said...
Carole has attended:
Excel VBA Intro Intermediate course
How to hide/show an embedded Word doc in Excel
I have a word doc embedded into an excel doc but I would like to make it only visible depending on the answer to a question. eg:
Do you like coffee? Yes/No (selected from dropdown)
In VBA:
If Range("D56") = "Yes" Then
Rows("58:61").EntireRow.Hidden = False
Else
Rows("58:61").EntireRow.Hidden = True
End If
Except, I need to substitute the Row Hidden command for the command to hide the Word doc.
Thanx
For upcoming training course dates see: Pricing & availability
RE: How to hide/show an embedded Word doc in Excel
Hello Carole
Thank you for your question regarding embedded word.
It sounds you have got most working, the combobox will return a value e.g. 1 for yes
here's code to show or hide the object, ensure you use the name of the object, where I have the name Object 5
If Sheets("Sheet3").Range("A1").Value = 1 Then
ActiveSheet.OLEObjects("Object 5").Visible = True
Else
ActiveSheet.OLEObjects("Object 5").Visible = False
End If
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Reply Wed 23 Feb 2011, 09:19Delegate Carole said...
RE: How to hide/show an embedded Word doc in Excel
Hi Mark
That works perfectly, thanx.
Howver, the icon (when displayed) has the text "Microsoft Word Picture" underneath.
How can I either remove this or edit & change to a more relevant text?
Thanx
Carole
Reply Wed 23 Feb 2011, 09:33Delegate Carole said...
RE: How to hide/show an embedded Word doc in Excel
Sorry Mark
I was just having a blonde moment!
I've amended the text using "Convert" on the Picture Object.
Thanx again
Carole
Reply Wed 23 Feb 2011, 16:43Delegate Carole said...
RE: How to hide/show an embedded Word doc in Excel
Hi
The code works fine but appears to have created another issue:
I am now unable to use the "paste" function anywhere on this sheet, except the cell I copy from.
I know it relates to this code as nothing else has been changed and, if I comment it out, I have the "paste" option again.
Protection is turned off.
Thanx
Carole
RE: How to hide/show an embedded Word doc in Excel
Hi Carole,
I'm not sure why you can't paste into the sheet since the code has nothing to do with the sheet properties. I created a workbook, inserted a Word document, then created the code to hide/unhide the object depending on the value placed in a particular cell. I used a data validation list, so perhaps it may be the Combo box that is causing the issue. I have copied from all the sheets with pasting successfully into the same sheet where the object is located. I also copied from another workbook and pasted with success.
Hopefully your problem has sorted itself out by now. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
|