Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

If / Call Sub

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
Seamus2626
Asked:
Seamus2626
  • 7
  • 5
  • 5
2 Solutions
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Seamus2626Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Seamus2626Author Commented:
Sure can,

Attached.

Thanks
Seamus
Example.xls
0
 
patrickabCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Seamus2626Author Commented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Yes, no problem I think.. you can try this ... if it is not the Q it will not call your sub..
0
 
patrickabCommented:
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
 
Seamus2626Author Commented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Hey seamus

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

Kind regards

Eric
0
 
patrickabCommented:
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
 
Seamus2626Author Commented:
Sorted now guys!!

Thanks
Seamus
0
 
patrickabCommented:
>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
 
patrickabCommented:
Seamus - Meanwhile, thanks for the points - Patrick
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now