Solved

If / Call Sub

Posted on 2011-02-24
17
332 Views
Last Modified: 2012-05-11
Hi,

I have a sub called "TestInsertPicture()"

I want to call this if cell L1 (Merged Cell) = the special character Q (In Wingdings2, its an X)

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 7
  • 5
  • 5
17 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34968682
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim myString As String
 Dim cel As Range, TARG As Range
 Dim KOLOM As Integer
Application.EnableEvents = False
On Error Resume Next
Set TARG = Intersect(Target, Range("L1"))     'Obviously you can change this target range to your desired range
KOLOM = TARG.Column
 If TARG Is Nothing Then
   Application.EnableEvents = True
   Exit Sub
   Else
   myString = TARG
end if
if mystring = "X" then call TestInsertPicture 'place here your special character you can use asscci code also..

End Sub
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34968685
That should do the job... You have to place it in the sheet macro of the sheet you want to supervise...


Kind regards

Eric
0
 

Author Comment

by:Seamus2626
ID: 34968727
Cant seem to get that to work, have tried the special character q and X

Where the special character is, its an If statement, can you change the code to call if the IF Statement is False?

Thanks
Seamus
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34968903
I could, can you poste an example then I will adapt the code to work with your special character.
Make sure it is in the file somewhere.

Kind regards

Eric
0
 

Author Comment

by:Seamus2626
ID: 34968949
Sure can,

Attached.

Thanks
Seamus
Example.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34969227
Perhaps the code below if placed in the code pane for Sheet("Screen") would do what you want.

BTW, the only value in merged cells is the one in the top left cell of the merged range.

Patrick
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Sheets("Screen").Cells(1, "L")

If Not Intersect(rng, Target) Is Nothing Then
    If Asc(rng) = 81 Then
        InsertPicture "\\ukhibmdata02\rights\\seamus\images.jpg", Range("M2"), True, True
    End If
End If

End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34969741
Dear seamus, try the attached file, it should work..
if you change in raw data the count and in Screen it sees a Q you will get a msg box and after you click ok the mac5ro will execute your sub..


Kind regards
Example-1-.xls
0
 

Author Comment

by:Seamus2626
ID: 34970569
Sorry guys, been away from my desk.

Patrick, nothing happens if i simply put that code into screen

Zwiekhorst, i cant have a message box unfortunately

Can the sub be executed if the IF statement in L1 is false?

Thanks
Seamus
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34970600
Yes, no problem I think.. you can try this ... if it is not the Q it will not call your sub..
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34970688
Seamus2626,

>Patrick, nothing happens if i simply put that code into screen

To install a sub in the code pane for a worksheet:
1) Right-click the worksheet tab
2) Select ‘View Code’
3) Paste the code in the worksheet VBA code pane
4) ALT + F11 to return to the worksheet

Patrick
0
 

Author Comment

by:Seamus2626
ID: 34970780
Thanks Patrick, i do know where to put the code. Its just nothing is happening when the L1 changes from Special Char P(Tick) to Q(Cross)

Whats the trigger in the code?

Zwiekhorst, im not really sure what that means. Excuse me though, i am a novice!
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 250 total points
ID: 34970820
Hey seamus

Just try all combinations for this cell.. it should work like you suggested..

Kind regards

Eric
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 250 total points
ID: 34970940
Seamus2626,

On my machine this works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Sheets("Screen").Cells(1, "L")

If Not Intersect(rng, Target) Is Nothing Then
    If Asc(rng) = 81 Then
        ActiveSheet.Pictures.Insert "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg"
    End If
End If

End Sub

As you know you cannot insert a picture into a specific cell. I'm afraid that is one of the shortcomings of Excel. If you want to attach a picture to a cell then you will need to insert the picture into a specific cell Comment.

Patrick
0
 

Author Closing Comment

by:Seamus2626
ID: 34971077
Sorted now guys!!

Thanks
Seamus
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34971163
>Whats the trigger in the code?

This is the trigger:

If Asc(rng) = 81 Then

where rng is defined as:

Set rng = Sheets("Screen").Cells(1, "L")

It is irrelevant that L1 is formatted with Wingdings as it is the Ascii character value that is checked for - and that's a Q. It ignores a P. A tick or a cross only show because the cell is formatted with Wingdings. A P or Q are what's entered manually and that is what is checked for by the VBA code.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34971173
Seamus - Meanwhile, thanks for the points - Patrick
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34977421
Thanks for the point Seamus,

And remember what Patrick told you of inserting a picture in the cell comment.
I tried that for a project of mine and this is working so much better than just pasting it to the sheet...


Kind regards

Eric
0

Featured Post

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

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 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

20 Experts available now in Live!

Get 1:1 Help Now