Solved

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

Posted on 2015-01-03
12
289 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Assisted Solution

by:simonwait
simonwait earned 0 total points
Comment Utility
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
Comment Utility
Try removing the .select
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Accepted Solution

by:
simonwait earned 0 total points
Comment Utility
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
Comment Utility
The select should not be necessary as it tends to slow down the code.
0
 
LVL 1

Author Comment

by:simonwait
Comment Utility
I did try that early on but it didn't work. I'm not sure why though.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now