Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
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" 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
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" 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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
Thanks Jim for your help - your code works great - much appreciated.
Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
Thanks JLGWhiz for your help - greatly appreciated.
Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
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" 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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbOKCancel Problem
Thanks Patrick for your help - much appreciated,
Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Override a VBOKCancel button? | Excel Programming | |||
changing vbOKCancel button title | Excel Discussion (Misc queries) | |||
Exiting Sub when Selecting Cancel on vbOKCancel | Excel Discussion (Misc queries) | |||
vbOKCancel box to close | Excel Programming | |||
Qn: vbOKCancel -- Cancel doesn't work?? | Excel Programming |