Embedded Excel File in Word Returns Method 'Range' of object'_Global' failed

Insurance Dave

New Member
Joined
Jul 1, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello All,

It has been a while since I have been here (had to create a new account). Good to be back. I have limited VBA knowledge (MrExcel and self-taught).

I have a Form - named 'Initial'- that opens when the workbook is opened. When opened from my PC there are no issues. The Excel file needs to be embedded into a Word Document for access to others. When it is embedded into a Word document and opened, I receive:

Run-time error '1004':
Method 'Range' of object'_Global' failed


Any ideas on why it only does this with the embedded version? Is there a better code for this?


The code for opening the Form is:

Private Sub Workbook_Open()
Initial.Show
End Sub


The form has two button options. One is to clear the data entry cells on a specific sheet. The other is to open the workbook with the previously saved data. The code is:

Private Sub Clear_Click()
With ThisWorkbook.Worksheets("Calculator")
Range("E6:F6").ClearContents
Range("B10:D10").ClearContents
Range("B16:D16").ClearContents
Range("B19:D19").ClearContents
Range("B22:D22").ClearContents
Range("B13:D13").ClearContents
Range("B25").ClearContents
End With
Unload Initial
End Sub


And the code to open with the previous data is just:

Private Sub OpenSheet_Click()
Unload Initial
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Your With block is currently not actually doing anything. The code should read:

Code:
Private Sub Clear_Click()
With ThisWorkbook.Worksheets("Calculator")
.Range("E6:F6").ClearContents
.Range("B10:D10").ClearContents
.Range("B16:D16").ClearContents
.Range("B19:D19").ClearContents
.Range("B22:D22").ClearContents
.Range("B13:D13").ClearContents
.Range("B25").ClearContents
End With
Unload Me
End Sub

and:
Code:
Private Sub OpenSheet_Click()
Unload Me
End Sub
 
Upvote 0
Hi Rory,

Thank you for your quick response. I have changed the code, and it does seem to work properly now. Thank you for your help!

Dave
 
Upvote 0
Glad to help. :)
 
Upvote 0

Forum statistics

Threads
1,214,720
Messages
6,121,124
Members
449,014
Latest member
Lilcps

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top