Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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

0
simonwait
Asked:
simonwait
  • 5
  • 4
3 Solutions
 
Saqib Husain, SyedEngineerCommented:
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

0
 
simonwaitAuthor Commented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
Oops, sorry it should have been

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

for each wb in workbooks
if wb.name=ComboBox21.Value then
    With wb.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 if
next wb

End Sub
                                  

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
simonwaitAuthor Commented:
OK ive got the 1st line to now work (#16).  It seems that the problem was I wasnt referencing the full path when finding the last cell.  Ive now called this a new variable LastCell and it works.   Now though I get Object Required on all the .ShapeRange lines.

Sub addWatermark()
    Dim StrIn As String

    StrIn = TextBox21.Text

    Bookext = ComboBox21.Value
    Book = Left(ComboBox21.Value, InStr(1, ComboBox21.Value, ".") - 1)
    Marksheet = ComboBox22.Value
    LastCell = Workbooks(Book).Sheets(Marksheet).UsedRange.Rows.Count

    If StrIn = "" Then Exit Sub

    For Each wb In Workbooks
        checkname = wb.Name
        If wb.Name = Bookext Then
            With Workbooks(Book).Sheets(Marksheet).Shapes.AddTextEffect(msoTextEffect21, StrIn, "+mn-lt", 54, msoTrue, msoFalse, 20, Range("A" & (LastCell)).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 If
    Next wb
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Try removing the .select
0
 
simonwaitAuthor Commented:
Ive managed to crack it.  Think it was all down to not being specific emough when talking about other workbooks.  VBA cant assume as much as it normally does.

Sub addWatermark()
    Dim StrIn As String

    StrIn = TextBox21.Text

    Bookext = ComboBox21.Value
    book = Left(ComboBox21.Value, InStr(1, ComboBox21.Value, ".") - 1)
    Marksheet = ComboBox22.Value
    Lastcell = Workbooks(book).Sheets(Marksheet).UsedRange.Rows.Count

    If StrIn = "" Then Exit Sub

    For Each wb In Workbooks
        checkname = wb.Name
        If wb.Name = Bookext Then
            Workbooks(book).Activate
            Workbooks(book).Sheets(Marksheet).Range(Workbooks(book).Sheets(Marksheet).Cells(1, 1), Workbooks(book).Sheets(Marksheet).Cells(1, 3)).Select
            f = Selection.Width
            With Workbooks(book).Sheets(Marksheet).Shapes.AddTextEffect(msoTextEffect21, StrIn, "+mn-lt", 54, msoTrue, msoFalse, 20, Range("A" & (Lastcell)).Top / 2).Select
                Selection.ShapeRange.Width = f - 50
                Selection.ShapeRange.TextFrame2.TextRange.Characters.Font.Fill.Transparency = 0.8
                Selection.ShapeRange.IncrementRotation 340.91445
            End With
        End If
    Next wb
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
The select should not be necessary as it tends to slow down the code.
0
 
simonwaitAuthor Commented:
I did try that early on but it didn't work. I'm not sure why though.
0
 
Martin LissRetired ProgrammerCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now