ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro Error (https://www.excelbanter.com/excel-programming/333063-macro-error.html)

Daniel R. Young
Macro Error
 
I have a macro that seems to be unfinished. I want a macro to activate when
a cell = "Yes". The macro will open the my pictures folder for someone to
insert a pic. I modified the macro so the picture will fit in the cells and
be resized. I have two of these macros on the same row but in different
columns. When I run the macro with the frist cell it works fine. The second
one however will place the picture in the first cell that I started with and
open the my picture document a second time. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ws_exit:
Application.EnableEvents = True

End Sub


Can someone assist me with this problem?

Thank you,

Daniel R. Young
Macro Error
 
SORRY HERE IS THE CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("$B$186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("$A$183:$D$191").Height
Selection.Width = Range("$A$183:$D$191").Width
Selection.Top = Range("$A$183:$D$191").Top
Selection.Left = Range("$A$183:$D$191").Left
Selection.Placement = xlMoveAndSize

End If

If Me.Range("$G$186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("$F$183:$I$191").Height
Selection.Width = Range("$F$183:$I$191").Width
Selection.Top = Range("$F$183:$I$191").Top
Selection.Left = Range("$F$183:$I$191").Left
Selection.Placement = xlMoveAndSize

End If
ws_exit:
Application.EnableEvents = True

"Daniel R. Young" wrote:

I have a macro that seems to be unfinished. I want a macro to activate when
a cell = "Yes". The macro will open the my pictures folder for someone to
insert a pic. I modified the macro so the picture will fit in the cells and
be resized. I have two of these macros on the same row but in different
columns. When I run the macro with the frist cell it works fine. The second
one however will place the picture in the first cell that I started with and
open the my picture document a second time. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ws_exit:
Application.EnableEvents = True

End Sub


Can someone assist me with this problem?

Thank you,

Tom Ogilvy
Macro Error
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Target.Address = "$B$186" Then
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ElseIf Target.Address = "$B$194" then
if me.Range("B194").Value = "Yes" then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A192:D200").Height
Selection.Width = Range("A192:D200").Width
Selection.Top = Range("A192:D200").Top
Selection.Left = Range("A192:D200").Left
Selection.Placement = xlMoveAndSize
End if
End If
ws_exit:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"Daniel R. Young" wrote in message
...
I have a macro that seems to be unfinished. I want a macro to activate
when
a cell = "Yes". The macro will open the my pictures folder for someone to
insert a pic. I modified the macro so the picture will fit in the cells
and
be resized. I have two of these macros on the same row but in different
columns. When I run the macro with the frist cell it works fine. The
second
one however will place the picture in the first cell that I started with
and
open the my picture document a second time. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ws_exit:
Application.EnableEvents = True

End Sub


Can someone assist me with this problem?

Thank you,

Tom Ogilvy
Macro Error
 
Just adapt my guess to match your specifics.

--
Regards,
Tom Ogilvy

"Daniel R. Young" wrote in message
...
SORRY HERE IS THE CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("$B$186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("$A$183:$D$191").Height
Selection.Width = Range("$A$183:$D$191").Width
Selection.Top = Range("$A$183:$D$191").Top
Selection.Left = Range("$A$183:$D$191").Left
Selection.Placement = xlMoveAndSize

End If

If Me.Range("$G$186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("$F$183:$I$191").Height
Selection.Width = Range("$F$183:$I$191").Width
Selection.Top = Range("$F$183:$I$191").Top
Selection.Left = Range("$F$183:$I$191").Left
Selection.Placement = xlMoveAndSize

End If
ws_exit:
Application.EnableEvents = True

"Daniel R. Young" wrote:

I have a macro that seems to be unfinished. I want a macro to activate
when
a cell = "Yes". The macro will open the my pictures folder for someone
to
insert a pic. I modified the macro so the picture will fit in the cells
and
be resized. I have two of these macros on the same row but in different
columns. When I run the macro with the frist cell it works fine. The
second
one however will place the picture in the first cell that I started with
and
open the my picture document a second time. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ws_exit:
Application.EnableEvents = True

End Sub


Can someone assist me with this problem?

Thank you,

Daniel R. Young
Macro Error
 
THANKS TOM!!!
I got it with this:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Target.Address = "$B$186" Then
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
End If
If Target.Address = "$G$186" Then
If Me.Range("G186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("F183:I191").Height
Selection.Width = Range("F183:I191").Width
Selection.Top = Range("F183:I191").Top
Selection.Left = Range("F183:I191").Left
Selection.Placement = xlMoveAndSize
End If
End If
ws_exit:
Application.EnableEvents = True

End Sub

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Target.Address = "$B$186" Then
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ElseIf Target.Address = "$B$194" then
if me.Range("B194").Value = "Yes" then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A192:D200").Height
Selection.Width = Range("A192:D200").Width
Selection.Top = Range("A192:D200").Top
Selection.Left = Range("A192:D200").Left
Selection.Placement = xlMoveAndSize
End if
End If
ws_exit:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"Daniel R. Young" wrote in message
...
I have a macro that seems to be unfinished. I want a macro to activate
when
a cell = "Yes". The macro will open the my pictures folder for someone to
insert a pic. I modified the macro so the picture will fit in the cells
and
be resized. I have two of these macros on the same row but in different
columns. When I run the macro with the frist cell it works fine. The
second
one however will place the picture in the first cell that I started with
and
open the my picture document a second time. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = True
If Me.Range("B186").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("A183:D191").Height
Selection.Width = Range("A183:D191").Width
Selection.Top = Range("A183:D191").Top
Selection.Left = Range("A183:D191").Left
Selection.Placement = xlMoveAndSize
End If
ws_exit:
Application.EnableEvents = True

End Sub


Can someone assist me with this problem?

Thank you,


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com