runtime error 104

[Redhawk]

Good day everyone.
I have written a macro, and linked it to a control, to open an existing workbook and then to insert text in a specific cell(group of cells, the cells are merged). Regardless of what i change i keep on getting a runtime error 1004.

Here is the code i used:

Private Sub CommandButton1_Click()

' INSERTWHSQINWO Macro
' This macro inserts the white melamine squareline finish in the works order

Workbooks.Open Filename:="E:\Leisure Kitchens\NCS\PM.xls", UpdateLinks _
:=3
Range("F4:J6").Select
ActiveCell.FormulaR1C1 = "WHITE MELAMINE SQUARELINE"
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

Any suggestions would be greatly appreciated.

Thanks
Red

 

[BrianB]

It is necessary to say on which line of code the error occurs.
I have tested your code without the 'Workbooks.Open' line and it works OK.
Perhaps you have got the path/filename wrong
perhaps you need to activate the correct worksheet.

 

[Redhawk]

Hi, thanks for the response.
The error occured, at
"Range("F4:J6").Select"

I have checked the path and file name and it is correct, hence my confusion

Red

 

[StevenD]

Just a quick suggestion. Have you tried...

ActiveWorkbook.range("F4:J6").select

 

[BrianB]

I think you must have the wrong range. Are you sure that those are the merged cells ? Are you sure that you have the correct worksheet active ?
As I said, my test worked OK.

 

[Andrew Poulsom]

I would check that the TakeFocusOnClick property of the CommandButton is set to False.

 

[Redhawk]

The take focus on click is set to false. The range is correct and the macro runs perfectly until i put it behind the control button. Then i still get the same range error as before???Bizzare or what?

I appreciate the effort

Thanks

Red

 

[erik.van.geit]

Bizzare or what?

not at all!
your buttoncode always refers to the sheet where it is located
Range(...) cannot be selected since it means
Sheets("sheetwherebuttonislocated").Range(...).select
since the sheet is not active you cannot select anything on it...

what you mean is to select cells on the sheet in the other workbook
so as Steve pointed out you probably need
ActiveWorkbook.range("F4:J6").select
EDIT: see correction below !

kind regards,
Erik

 

[Norie]

Try this

ActiveWorkbook.ActiveSheet.Range("F4:J6").Select

And so did this.

    With ActiveWorkbook.ActiveSheet.Range("F4:J6")
        .Value = "WHITE MELAMINE SQUARELINE"
        With .Font
            .Name = "Tahoma"
            .FontStyle = "Regular"
            .Size = 14
        End With
    End With

Erik

There is no Range method/object of a workbook.

 

[erik.van.geit]

Norie,
thanks for correcting this
if you read my explanation

what you mean is to select cells on the sheet in the other workbook

there is
what you mean is to select cells on the sheet in the other workbook
I should have verified the syntax which was provided earlier, so this would have been what was correctly formulated in words

kind regards,
Erik