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.

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.

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 the 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.

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 the 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.

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 the 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.

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 the 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.

Perfection as usual! Thanks so much Bernie!

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 the 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.

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 the 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.

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.