Code adaption help please

ADAMC

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Hi all, as always thanks for any help in advance.

I have searched the bored and almost found some code to what i require.
The code was written by Datsmart:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oPic As Picture
    Me.Pictures.Visible = False
    With Target
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Offset(0, 1).Top
                oPic.Left = .Offset(-1, 1).Left
                Exit For
            End If
        Next oPic
    End With
End Sub

Basically i have 4 pictures somewhere on my sheet of 4 flags one red one green one ornge an one grey.
They work like flags in outlook, green complete an so on.

Using the code above if i type red in a cell the pic of red flag appears to the right which is what i want but if i go down to the next row and type red for the next action point, the flag above disappears and appears to the right of where i last wrote the text....

Can the code be adapted so the flag stays for any instance of the typed word...

So if i type red my red pic appears when i tab to the next row and type red the pic doesnt move from the previous row....?

Hope this makes sense.

Thanks
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi all different approach.....

Is it possible to have 1 button.....when i click that button it cycles through 4 pictures i have somewhere on the worksheet?

1 click shows pic 1 1 cell to right
2 clicks shows pic 2 1 cell to right
3 clicks shows pic 3 1 cell to right
4th click shows pic 4 1 cell to right
5th click back to pic 1 1 cell to right....?

Thanks
 
Upvote 0
A way of possibly doing this is storing a value on the sheet somewhere as a counter.

Like:

Code: 
Private Sub CommandButton1_Click()
If Range("A1").value = 4 then Range("A1").value = 0
Range("A1").value = Range("A1").value + 1

'Use select statements to perform the tasks you want done with the picture based on what value is in A1.  You could use the If above or use the function Mod(Range("A1").value,4) and use those values within the select statement?

End Sub

This is one approach. I believe you can also store values within excel for future uses, but I don't know exactly how to do that.

Hopefully that may help some or give you some ideas.
 
Upvote 0
schielrn

Thanks, ill be honest, your code lost me a little. i was thinking perhaps a toggle button.....and storing the pics in either a folder on my desktop somewhere or storing them on the sheet...VB is not really one of my strong points so please help!!!

:-D
 
Upvote 0
If you have the pictures set up on the worksheet in the places that you would like them then try this:

Try putting in a command button. Then double click on it and insert the code below. The code is based on 4 pictures.

Code: 
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Me.Pictures.Visible = False
Range("A1").Value = Range("A1").Value + 1
caseValue = (Range("A1").Value) Mod 4

Select Case (caseValue)

Case 1:
Pictures(1).Visible = True

Case 2:
Pictures(2).Visible = True

Case 3:
Pictures(3).Visible = True

Case 0:
Pictures(4).Visible = True
End Select

CommandButton1.Visible = True

Application.ScreenUpdating = True

End Sub

Maybe this might get you started, but I'm not exactly sure what you are trying to do with the pictures, but this will just make them appear in sequence. Not sure if that is what you are after?
 
Upvote 0
One other thing that can be done is change the caption of the button in the code to display what color flag is going to be show next?
 
Upvote 0
Thanks for that sure it will get me going! :-D

Basically, in column e i have questions to be answered.

The user will then assign a flag to the question (the 4 pictures)
Red- incomplete flag an so on

I wanted to put the button over column C so the user could click and in column D next to the button the appropriate picture would appear.

So in E1 i answer a question but dont finish it...i go to column C...i click button till the orange pic appears...i can now close worksheet.

Thanks
 
Upvote 0
You may want to hide the value in A1 like in C1 behind the button, just so it isn't seen.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Please test this code to check if a variable number of columns are all equal
Replies
2
Views
169
JenniferMurphy
JenniferMurphy
J
  • Question Question
Multiple conditional formatting
Replies
1
Views
46
StephenCrump
S
N
  • Question Question
Stacked Chart to show value and status YTD
Replies
2
Views
793
neilo1969
N
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
312
Minh Tuan VN
M
I
  • Question Question
Code runs out of line & edit advice needed please
Replies
2
Views
284
Micron
M

Forum statistics

Threads
1,223,431
Messages
6,172,089
Members
452,444
Latest member
ShaImran193

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top