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