Toggling Visibility of items with VBA

S

SpielbergRules

Hello,

New to this forum. Created my own invoice for my business and I am
trying to toggle an image that will appear based on the fact of the
invoice being an invoice or a packing list. (You know how they cover
the price and cost columns of a packing list you'd receive when you buy
something.)

In one cell, it will actually say "INVOICE" or "PACKING LIST". I would
like the filled rectangles to be toggled based on those specific words.

I can't seem to be able to make any kind of formula to check that and
make it invisible.

Does anyone know a simple way to do that?

Thanks in advance for any help.

Mike
 
D

davesexcel

There is a way to insert an image by selecting an item in a dropdown
menu

I cannot find the place I found the example so I will have to go by
memory, I know it was a MVP site and I am sure it was JD M....
but I can't locate it

-Open a new workbook
-insert three or four images anywhere in sheet 1
-enter this code into sheet 1's worksheet module
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With

End Sub

sheet 2 enter this list

columnA Column B
selection 1 Picture 1
selection 2 Picture 2
selection 3 Picture 3

Name the range PicTable

go back to sheet 1

Select sheet1 A2
goto DATA,Validation, list
in th source box enter this formula

=offset(PicTable,,,,1)

select sheet1 F1 and enter this function

=VLOOKUP(A2,PicTable,2,FALSE)

hoping this helps you out...


+-------------------------------------------------------------------+
|Filename: Select BullNose.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4966 |
+-------------------------------------------------------------------+
 
S

SpielbergRules

Thanks for that more than instantaneous reply! I will try that right
now.

Mike
 
B

Bob Phillips

Here is one way.

Paste both images onto the sheet both in the same print position, and name
them pic_INVOICE and pic_PACKING.

Make them non-visible.

Then add this code, based on the asusmption that INVOICE or PACKING LIST is
in $A$1 (change to suit)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape
On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = "INVOICE" Then
Me.Shapes("pic_INVOICE").Visible = True
Me.Shapes("pic_PACKING").Visible = False
Else
Me.Shapes("pic_INVOICE").Visible = False
Me.Shapes("pic_PACKING").Visible = True
End If
End If


ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SpielbergRules"
 
Top