Solved

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

Posted on 2015-01-03
12
315 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 100 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 46

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

825 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