Solved

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

Posted on 2015-01-03
12
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 48

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

624 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