Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-03
12
Medium Priority
?
386 Views
Last Modified: 2015-01-28
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
Comment
Question by:simonwait
  • 5
  • 4
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40529928
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
 
LVL 1

Author Comment

by:simonwait
ID: 40530031
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 40530046
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
Technology Partners: 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!

 
LVL 1

Assisted Solution

by:simonwait
simonwait earned 0 total points
ID: 40530056
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40530061
Try removing the .select
0
 
LVL 1

Accepted Solution

by:
simonwait earned 0 total points
ID: 40530096
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40530102
The select should not be necessary as it tends to slow down the code.
0
 
LVL 1

Author Comment

by:simonwait
ID: 40530137
I did try that early on but it didn't work. I'm not sure why though.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40574342
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40574343
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question