vbOKCancel Problem

[Chris]

Hi, could someone please help me with the following code: when I click
on the OK button the code simply exits the subroutine. What I need is
for the user to click on the cancel button and then the subroutine
exits. However, if the user clicks on the OK buton, I need the
subroutine to continue to the end.

Any help would be greatly appreciated.

Kind regards,

Chris.

Sub copylastrowtocolVdown()

Dim lr As Long

Application.ScreenUpdating = False

MsgBox "Copy Last Record Down when the last Position Data record has
been populated in readiness to be copied into the last blank row
below.", vbOKCancel, "Copy Last Record Down"

If vbCancel Then Exit Sub

lr = Cells(Rows.Count, "a").End(xlUp).Row
Cells(lr, 1).Resize(, 22).Copy Cells(lr, 1).Resize(2)

Application.ScreenUpdating = True

End Sub

 

[Jim Cone]

Msgbox is a function and you can use it like any other Excel function.
Screenupdating should be on while the msgbox is displayed...
'--
Sub copylastrowtocolVdown()
Dim lr As Long
lr = MsgBox("Copy Last Record down after the last record is entered. ", _
vbOKCancel, "Copy Last Record Down")
If lr = vbCancel Then Exit Sub

Application.ScreenUpdating = False
lr = Cells(Rows.Count, "a").End(xlUp).Row
Cells(lr, 1).Resize(, 22).Copy Cells(lr, 1).Resize(2)
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA


"Chris" <[email protected]>
wrote in message
Hi, could someone please help me with the following code: when I click
on the OK button the code simply exits the subroutine. What I need is
for the user to click on the cancel button and then the subroutine
exits. However, if the user clicks on the OK buton, I need the
subroutine to continue to the end.

Any help would be greatly appreciated.

Kind regards,

Chris.

Sub copylastrowtocolVdown()

Dim lr As Long

Application.ScreenUpdating = False

MsgBox "Copy Last Record Down when the last Position Data record has
been populated in readiness to be copied into the last blank row
below.", vbOKCancel, "Copy Last Record Down"

If vbCancel Then Exit Sub

lr = Cells(Rows.Count, "a").End(xlUp).Row
Cells(lr, 1).Resize(, 22).Copy Cells(lr, 1).Resize(2)

Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

Resp = MsgBox("Copy Last Record Down when the last" _
"Position Data record has been populated in" _
"readiness to be copied into the last blank" _
"row below.", vbOKCancel, "Copy Last Record Down")

If Resp = vbCancel Then Exit Sub

 

[Chris]

Thanks Jim for your help - your code works great - much appreciated.

Cheers,

Chris.

 

[Chris]

Thanks JLGWhiz for your help - greatly appreciated.

Cheers,

Chris.

 

[Patrick Molloy]

Sub copylastrowtocolVdown()

Dim lr As Long

Application.ScreenUpdating = False

IF MsgBox("Copy Last Record Down when the last Position Data record has been
populated in readiness to be copied into the last blank rowbelow." _
, vbOKCancel, "Copy Last Record Down" ) = vbCancel THEN
Exit Sub
End If
lr = Cells(Rows.Count, "a").End(xlUp).Row
Cells(lr, 1).Resize(, 22).Copy Cells(lr, 1).Resize(2)

Application.ScreenUpdating = True

End Sub

 

[Chris]

Thanks Patrick for your help - much appreciated,

Kind regards,

Chris.