Please Help - VBA Change Event for Excel

[DWC via OfficeKB.com]

Hi

Firstly, thanks for your consideration.

I have a workbook with two worksheets. I wish to set up an automation that
cuts a row from sheet 1, pastes that row into row one of sheet 2 then deletes
the now blank row from sheet 1.

The trigger for this event is when users select the string - "3. Finalised" -
made available from a validation list in column H of sheet 1. What I wish to
happen at that stage is for a msgbox to automatically appear advising the
user that if they click "OK" in the msgbox, the row will be transferred (as
described above) and that, should they wish for this NOT to happen, then they
must exit (ESC?) and select another string other than
"3. Finalised".

I hope this makes sense and I kindly ask for your wisdom.

BTW, I have been trying to get my head around VBA (absolute novice) but am
finding it difficult - if you can share a link that is like "VBA for Dummies"
I would be much appreciated.

Many thanks in advance...

--
Message posted via http://www.officekb.com

[DWC via OfficeKB.com]

EOM

--
Message posted via http://www.officekb.com

[DWC via OfficeKB.com]

C'mon Gang, would really appreciate your wisdom here

--
Message posted via http://www.officekb.com

[DWC via OfficeKB.com]

EOM

--
Message posted via http://www.officekb.com

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nResult As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Columns(8)) Is Nothing Then
If .Text Like "Finalized" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
With Rows(.Row)
.Copy Destination:=Sheets("Sheet2").Rows(1)
.Delete Shift:=xlUp
End With
End If
End If
End If
End With
End Sub

Note that the Worksheet_Change event will not fire when a selection is
made from a validation dropdown in XL97 and MacXL (don't remember about
XL00). For compatibility with those versions, use the _Calculate event
and check your range for "Finalized".



In article <58656529acd2d@uwe>, "DWC via OfficeKB.com" <u16385@uwe>
wrote:

> Hi
>
> Firstly, thanks for your consideration.
>
> I have a workbook with two worksheets. I wish to set up an automation that
> cuts a row from sheet 1, pastes that row into row one of sheet 2 then deletes
> the now blank row from sheet 1.
>
> The trigger for this event is when users select the string - "3. Finalised" -
> made available from a validation list in column H of sheet 1. What I wish to
> happen at that stage is for a msgbox to automatically appear advising the
> user that if they click "OK" in the msgbox, the row will be transferred (as
> described above) and that, should they wish for this NOT to happen, then they
> must exit (ESC?) and select another string other than
> "3. Finalised".
>
> I hope this makes sense and I kindly ask for your wisdom.
>
> BTW, I have been trying to get my head around VBA (absolute novice) but am
> finding it difficult - if you can share a link that is like "VBA for Dummies"
> I would be much appreciated.
>
> Many thanks in advance...

[DWC via OfficeKB.com]

Hi JE

Many thanks for the prompt reply and based on my description, your code
worked - cheers!

Now that I have run it, I do need seek advice on tweeking the code as follows:


1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
it should clear so that the user knows they must choose another selection
from the drop down list.

2. The automated cut and paste to sheet 2 must retain previously pasted rows -
your code currently overwrites prevous entries - i.e. all finalised rows must
remain in sheet 2.

JE can you Assist?

JE McGimpsey wrote:
>One way:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim nResult As Long
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(.Cells, Columns(8)) Is Nothing Then
> If .Text Like "Finalized" Then
> nResult = MsgBox(Prompt:= _
> "Clicking yes will move this line to sheet 2", _
> Title:="Are you sure?", _
> Buttons:=vbYesNo)
> If nResult = vbYes Then
> With Rows(.Row)
> .Copy Destination:=Sheets("Sheet2").Rows(1)
> .Delete Shift:=xlUp
> End With
> End If
> End If
> End If
> End With
> End Sub
>
>Note that the Worksheet_Change event will not fire when a selection is
>made from a validation dropdown in XL97 and MacXL (don't remember about
>XL00). For compatibility with those versions, use the _Calculate event
>and check your range for "Finalized".
>
>> Hi
>>
>[quoted text clipped - 19 lines]
>>
>> Many thanks in advance...

--
Message posted via http://www.officekb.com

[DWC via OfficeKB.com]

JE? Anyone? Please help...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

[Dave Peterson]

Maybe this'll be closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nResult As Long
Dim DestCell As Range

On Error GoTo errHandler:

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
If LCase(.Text) Like "*finalized*" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
Application.EnableEvents = False
If nResult = vbYes Then
With Sheets("sheet2")
Set DestCell = .Range("A" & _
.Cells(.Rows.Count, 8).End(xlUp).Row + 1)
End With
With Me.Rows(.Row)
.Copy _
Destination:=DestCell
.Delete Shift:=xlUp
End With
Else
.ClearContents
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

"DWC via OfficeKB.com" wrote:
>
> Hi JE
>
> Many thanks for the prompt reply and based on my description, your code
> worked - cheers!
>
> Now that I have run it, I do need seek advice on tweeking the code as follows:
>
> 1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
> it should clear so that the user knows they must choose another selection
> from the drop down list.
>
> 2. The automated cut and paste to sheet 2 must retain previously pasted rows -
> your code currently overwrites prevous entries - i.e. all finalised rows must
> remain in sheet 2.
>
> JE can you Assist?
>
> JE McGimpsey wrote:
> >One way:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > Dim nResult As Long
> > With Target
> > If .Count > 1 Then Exit Sub
> > If Not Intersect(.Cells, Columns(8)) Is Nothing Then
> > If .Text Like "Finalized" Then
> > nResult = MsgBox(Prompt:= _
> > "Clicking yes will move this line to sheet 2", _
> > Title:="Are you sure?", _
> > Buttons:=vbYesNo)
> > If nResult = vbYes Then
> > With Rows(.Row)
> > .Copy Destination:=Sheets("Sheet2").Rows(1)
> > .Delete Shift:=xlUp
> > End With
> > End If
> > End If
> > End If
> > End With
> > End Sub
> >
> >Note that the Worksheet_Change event will not fire when a selection is
> >made from a validation dropdown in XL97 and MacXL (don't remember about
> >XL00). For compatibility with those versions, use the _Calculate event
> >and check your range for "Finalized".
> >
> >> Hi
> >>
> >[quoted text clipped - 19 lines]
> >>
> >> Many thanks in advance...
>
> --
> Message posted via http://www.officekb.com

--

Dave Peterson

[DWC via OfficeKB.com]

Hi David

Many thanks for the reply and time taken...

I have inserted yuor code but it does not seem to be triggering anything when
I make the change to sheet1 column 8. I am currently trying a hybrid between
your and JE's codes - will be more specific if I can isolate anything.

Cheers!



Dave Peterson wrote:
>Maybe this'll be closer:
>
>Option Explicit
>Private Sub Worksheet_Change(ByVal Target As Range)
> Dim nResult As Long
> Dim DestCell As Range
>
> On Error GoTo errHandler:
>
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
> If LCase(.Text) Like "*finalized*" Then
> nResult = MsgBox(Prompt:= _
> "Clicking yes will move this line to sheet 2", _
> Title:="Are you sure?", _
> Buttons:=vbYesNo)
> Application.EnableEvents = False
> If nResult = vbYes Then
> With Sheets("sheet2")
> Set DestCell = .Range("A" & _
> .Cells(.Rows.Count, 8).End(xlUp).Row + 1)
> End With
> With Me.Rows(.Row)
> .Copy _
> Destination:=DestCell
> .Delete Shift:=xlUp
> End With
> Else
> .ClearContents
> End If
> End If
> End If
> End With
>
>errHandler:
> Application.EnableEvents = True
>End Sub
>
>> Hi JE
>>
>[quoted text clipped - 46 lines]
>> >>
>> >> Many thanks in advance...
>

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

[Dave Peterson]

What version of xl are you running?

(Read JE's notes if you're running xl97.)

"DWC via OfficeKB.com" wrote:
>
> Hi David
>
> Many thanks for the reply and time taken...
>
> I have inserted yuor code but it does not seem to be triggering anything when
> I make the change to sheet1 column 8. I am currently trying a hybrid between
> your and JE's codes - will be more specific if I can isolate anything.
>
> Cheers!
>
> Dave Peterson wrote:
> >Maybe this'll be closer:
> >
> >Option Explicit
> >Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim nResult As Long
> > Dim DestCell As Range
> >
> > On Error GoTo errHandler:
> >
> > With Target
> > If .Count > 1 Then Exit Sub
> > If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
> > If LCase(.Text) Like "*finalized*" Then
> > nResult = MsgBox(Prompt:= _
> > "Clicking yes will move this line to sheet 2", _
> > Title:="Are you sure?", _
> > Buttons:=vbYesNo)
> > Application.EnableEvents = False
> > If nResult = vbYes Then
> > With Sheets("sheet2")
> > Set DestCell = .Range("A" & _
> > .Cells(.Rows.Count, 8).End(xlUp).Row + 1)
> > End With
> > With Me.Rows(.Row)
> > .Copy _
> > Destination:=DestCell
> > .Delete Shift:=xlUp
> > End With
> > Else
> > .ClearContents
> > End If
> > End If
> > End If
> > End With
> >
> >errHandler:
> > Application.EnableEvents = True
> >End Sub
> >
> >> Hi JE
> >>
> >[quoted text clipped - 46 lines]
> >> >>
> >> >> Many thanks in advance...
> >
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200512/1

--

Dave Peterson