Posted to microsoft.public.excel.programming
|
|
Macro to activate VB Code
Larry,
If you still have my email address, send me the workbook (take out any
private information) and I will take a look.
Bernie
"Larry" wrote in message
...
Quick Question: The AutoShapes I am using are rounded rectangles. The
'TurnOff' macro is assigned to rounded rectangle "Data Mode". The
'TurnOn'
macro is assigned to rounded rectangle "Poster Mode". I have changed
"AutoShape1/2" to 'Data Mode', 'Poster Mode', 'Rounded Rectangle',
'Rounded
Rectangle 1', 'TurnOff'and 'TurnOn'. Each time I attempt to run the
macro
by clicking on the AutoShape, I get the error "The item with the specified
name wasn't found" The TurnOff and TurnOn macros work great otherwise.
Any
suggestions?
Regards,
Larry
"Bernie Deitrick" wrote:
Larry,
Add this to one macro (changing the "AutoShape 1/2" to match your actual
names of the objects the macros are assigned to):
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.Visible = msoFalse
ActiveSheet.Shapes("AutoShape 2").Select
Selection.ShapeRange.Fill.Visible = msoTrue
myCell.Select
and this to the other
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
ActiveSheet.Shapes("AutoShape 2").Select
Selection.ShapeRange.Fill.Visible = msoFalse
myCell.Select
Set the colors as you want: one of the shapes will always be colored.
Of
course, you will need to figure out the proper logic to get it to behave
the
way you want.
HTH,
Bernie
MS Excel MVP
"Larry" wrote in message
...
Thanks Bernie, it works great! Is there a way to make the backgrounds
of
the
"Data Entry Mode" drawing object and "Poster Mode" drawing object
change
color when clicked on, then remove the backround color when the
opposite
button is clicked. e.g. click on "Data Entry Mode" object and it turns
green,
when you click on "Poster Mode" object it turns green and the "Data
Entry
Mode" object reverts to a white background, and vice-versa?
Regards,
Larry
"Bernie Deitrick" wrote:
Larry,
Put this code into a standard code module (NOT the same place where
you
have
the Worksheet_SelectionChange event).
Sub TurnOff()
Application.EnableEvents = False
End Sub
Sub TurnOn()
Application.EnableEvents = True
End Sub
Then put two drawing objects, one withe text "Data Entry Mode", the
other
with text "Poster Mode": Assign the first macro to the first drawing
object,
and the second macro to the second drawing object, and you can click
on
the
appropriate one prior to doing what you need to do.
The other option is to do your entry somewhere else, then select
multiple
cells and drag them into the first column. Since your code checks
for
multiple cells and stops, that would take care of it.
HTH,
Bernie
MS Excel MVP
"Larry" wrote in message
...
Thanks Bernie. Here is the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
On Error Resume Next
If Target.Column < 1 Then Exit Sub
Dim myName As String
Dim rCells As Range
myName = "D:\Winners Picture\" & _
Target.Value & ".jpg"
MsgBox myName, vbMsgBoxLeft, "Print Winner Poster"
Application.EnableEvents = False
With Worksheets("Winner Poster")
.Range("A1").Value = Target.Value
On Error Resume Next
.Shapes("Inserted").Delete
On Error GoTo 0
Set rCells = .Range("C11:I29")
With .Pictures.Insert(myName)
.Top = rCells.Top
.Left = rCells.Left
.Width = rCells.Width
.Height = rCells.Height
.Name = "Inserted"
.Placement = xlMoveAndSize
End With
End With
Worksheets("Winner Photo Log").Select
Application.EnableEvents = True
Application.Calculate
If MsgBox("Do you want to Preview prior to printing?", vbYesNo) =
vbYes
Then
Worksheets("Winner Poster").PrintPreview
Else
Worksheets("Winner Poster").Select
Application.ActivePrinter = "hp photosmart P1100 series on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
"hp photosmart P1100 series on LPT1:", Collate:=True
Worksheets("Winner Photo Log").Select
End If
End Sub
I must disable the macros when opening the spreadsheet to enter
information
in the cells in Column A. After entering and saving the info, I
must
exit
the spreadsheet, reopen and enable macros in order for the program
to
select
information & photos. If the spreadsheet is opened with macros
enabled,
and
you click on cell A5 as an example, the program looks for insert
properties
instead of allowing you to enter info. This program will be used
by
many
people. Ideally, I'd like to place a "Poster Mode" button or bar
in
cell
E6
of the 'Winner Photo Log' which will allow the user to activate th
e VB
code.
This would eliminate the need to exit and re-enter the program.
Thanks
for
your help!
"Bernie Deitrick" wrote:
Larry,
What you really need to do is re-write your macros/events to be
more
selective so that you can enter data into the active cells. It
would
help
if you posted your code, and described what actually occcurs
when
you
enter
your data.
HTH,
Bernie
MS Excel MVP
"Larry" wrote in message
...
I have a worksheet which activates a VB Code when the user
selects
"enable
macros" from the prompt box at startup. To enter information
into
the
active
cells, we must "disable macros" from the prompt box at
startup.
Once the user has entered and saved information into the
active
cells,
the
user must exit the program, reenter the program and select
"enable
macros"
to
activate the VB Code.
On the worksheet "Winner Photo Log", I would like to insert a
macro so
that,
when the user clicks cell E6, the VB Code will activate (i.e.
"enable
macros"). This will eliminate the step of exiting and
reentering
the
program. Thanks in advance for any help you may be able
offer.
|