vba + image + conditional?

[tomatosalsa]

Hi! I'm new to this, but i've surfed the forums and can't find an answer to my problem.

In cell (A1), I have a dropdown box with two options: GOLD or SILVER.

Basically, if SILVER is chosen in that cell, then I want a watermark (or mimic watermark using wordart?) to be displayed across the screen.
If GOLD is chosen, then nothing will happen.

I've tried surfing through google and different excel forums, but the only thing i can find is HOW to create the mimic watermark. What i want is to make it a conditional watermark/image...

Please help. I'm currently using Excel 2000 (i know its outdated, but not my choice :P)

 

[wolverineb]

I would do something like this - it will need to be adjusted based on what you want, where your cell is, etc.:

<Code>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("C3").Address Then
If Target.Value = "SILVER" Then
ActiveSheet.SetBackgroundPicture Filename:= _
"C:\Documents and Settings\My Documents\mypicture.JPG"
Else
ActiveSheet.SetBackgroundPicture Filename:=""
End If
End If
End Sub
</Code>

 

[tomatosalsa]

this is the code i currently have to mimic the watermark.
i dont know how to make it conditional when i select 'SILVER' from the dropdown.....

thanks!!!!


Sub RatingSilver()
ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect2, _
Text:="DRAFT - Not For Release", FontName:="Arial Black", FontSize:=36, _
FontBold:=False, FontItalic:=False, Left:=100, Top:=150).Select
' define the text dimensions
With Selection.ShapeRange
.ScaleHeight 1.23, False
.ScaleWidth 1.6, False

' Solid or no color
.Fill.Visible = True
.Fill.Solid
.Fill.ForeColor.SchemeColor = 22
.Fill.Transparency = 0.5

' Outline
.Line.Weight = 1#
.Line.DashStyle = 7
.Line.Style = 1
.Line.Transparency = 0#
.Line.Visible = True
.Line.ForeColor.SchemeColor = 22
.Line.BackColor.RGB = RGB(255, 255, 255)

.Height = 300
.Width = 650
End With
End Sub

 

[wolverineb]

I would still put it in the Worksheet_Change event code area and just have it call your Sub from there. Let me know if that makes sense. I'm a little sleepy.

 

[tomatosalsa]

sorry but no idea what that means..?
despite the code that i've got there..i actually dont know much excel haha..

it keeps asking me to assign macro name?

 

[wolverineb]

If your code does what you want and you're just trying to trigger it, then do this:

Right click on the worksheet's tab and select "View Code." Then paste the code below (or something like it) and close the editor to go back to your worksheet. Try it out and see if it works.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
Application.EnableEvents = False 'Stops changes from retriggering this event
If UCase(Target.Value) = "SILVER" Then
'Run your macro:
RatingSilver
Else
'You should put something here to clear all that formatting
'if the user selects something other than SILVER
End If
End If
Application.EnableEvents = True
End Sub

 

[blmikolich]

Hi!

I am workign on something very similar, however, I have an image within another sheet, which I have named "Prelim Data". How would I make that image the watermark?

Thanks

 

[wolverineb]

Not sure what you mean exactly, but maybe record a macro while you select that other image, then view the code to see how Excel referred to it. Then adjust the above macro accordingly.

HTH. No time to go into detail today.