asked on

Trying to add watermark to excel sheet in another workbook with vba

Hi I am making a macro with a couple of comboboxes and a textbox. Combo 1 has the names of all open workbooks. Combo 2 populates with the sheets in the book selected in the 1st combobox. On the click on a button the macro should generate a watermark on the selected sheet using the text in the textbox. Everything is going ok but now I'm trying to add the watermark and getting an subscript out of range message. I've tried loosing the extension when referencing the workbook but that doesn't help.

Sub addWatermark()
    Dim StrIn As String

    StrIn = TextBox21.Text

    Book = Left(ComboBox21.Value, InStr(1, ComboBox21.Value, ".") - 1)
    Marksheet = ComboBox22.Value

    If StrIn = "" Then Exit Sub

    With Workbooks(Book).Sheets(Marksheet).Shapes.AddTextEffect(msoTextEffect21, StrIn, _
                                                                "+mn-lt", 54, msoTrue, msoFalse, 20, Range("A" & (Sheets(Marksheet).UsedRange.Rows.Count)).Top / 2)    '.Select
        .ShapeRange.IncrementRotation 340.91445
        .ShapeRange.ScaleWidth 1.4958350677, msoFalse, msoScaleFromTopLeft
        .ShapeRange.ScaleHeight 1.4189866141, msoFalse, msoScaleFromBottomRight
        .ShapeRange.TextFrame2.TextRange.Characters.Font.Fill.Transparency = 0.8

    End With

End Sub

Open in new window

Saqib Husain

I have had this problem in the past and I got away with something like

For Each wb In Workbooks
If wb.Name = combobx21.Value Then
    With Workbooks("ComboBox21.xlsx").Sheets("Marksheet").Shapes.AddTextEffect(msoTextEffect21, StrIn, _
                                                                "+mn-lt", 54, msoTrue, msoFalse, 20, Range("A" & (Sheets(Marksheet).UsedRange.Rows.Count)).Top / 2)    '.Select
End If
Next wb

Open in new window

simonwait

ASKER

Thanks for that, I changed the variables taking out the speech marks so it saw the variable rather than the string. It still gives the same error though im afraid.

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

simonwait

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

Try removing the .select

ASKER CERTIFIED SOLUTION

simonwait

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

The select should not be necessary as it tends to slow down the code.

simonwait

ASKER

I did try that early on but it didn't work. I'm not sure why though.

Martin Liss

I've requested that this question be closed as follows:

Accepted answer: 0 points for simonwait's comment #a40530056

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Saqib Husain

The basic solution was provided in http:#a40530046 Which should be an accept.

http:#a40530056 was a mere adaptation to local file (which was not provided) and should be an assist.