[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • 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 ZwiekhorstCommented:
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 ZwiekhorstCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Eric ZwiekhorstCommented:
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 ZwiekhorstCommented:
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 ZwiekhorstCommented:
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 ZwiekhorstCommented:
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 ZwiekhorstCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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