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.