Command Button Changes Data Each Time It Is Pressed - Want it to workonce per worksheet.
I want to prevent a user from accidentally changing the data more than
once but keep in mind the worksheet is copied every day on to a new tab with the current date on it (so it must be prevented only on that worksheet). Here is the current code: Private Sub CommandButton58_Click() Range("P203:S232").Select Selection.Copy Range("P202:S231").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False ActiveSheet.Select ActiveWorkbook.SendMail Recipients:=Range("i12", "i13"), Subject:=Range("i11") ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub I was thinking of adding code that would check a cell value, if blank then change to "Done" and copy the range, if not blank (i.e., if user clicks again) then skip copying of cell range. How do I write this and is this a good way to do it? Thanks! |
Command Button Changes Data Each Time It Is Pressed - Want it to work once per worksheet.
Your suggested method would work if you do something like
Private Sub CommandButton58_Click() If Range("A1").Value < "Done" then ---- your code here----- Range("A1").Value = "Done" Else MsgBox("Can't do that on this sheet") End If End Sub An alternative might be to compare the sheet name to the current date and enable/disable the button accordingly. The exact method for this would depend on the format of the date on the sheet name Ian wrote in message ... I want to prevent a user from accidentally changing the data more than once but keep in mind the worksheet is copied every day on to a new tab with the current date on it (so it must be prevented only on that worksheet). Here is the current code: Private Sub CommandButton58_Click() Range("P203:S232").Select Selection.Copy Range("P202:S231").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False ActiveSheet.Select ActiveWorkbook.SendMail Recipients:=Range("i12", "i13"), Subject:=Range("i11") ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub I was thinking of adding code that would check a cell value, if blank then change to "Done" and copy the range, if not blank (i.e., if user clicks again) then skip copying of cell range. How do I write this and is this a good way to do it? Thanks! |
Command Button Changes Data Each Time It Is Pressed - Want it towork once per worksheet.
Thanks Ian,
I had to modify it a little as making A1="Done" means that it will carry over when the sheet is copied. As I have a macro to copy the sheet it will now delete the A1 cell on the new sheet. Cheers |
Command Button Changes Data Each Time It Is Pressed - Want it to work once per worksheet.
Hadn't thought that far ahead, but glad it worked.
Ian wrote in message ... Thanks Ian, I had to modify it a little as making A1="Done" means that it will carry over when the sheet is copied. As I have a macro to copy the sheet it will now delete the A1 cell on the new sheet. Cheers |
All times are GMT +1. The time now is 06:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com