[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

If / Call Sub

Posted on 2011-02-24
17
Medium Priority
?
339 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 1000 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 1000 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 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