Lock A Row With An Entry In Col B On Saving Or Closing Workbook
Feb 15, 2009
I have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
View 11 Replies
ADVERTISEMENT
Nov 27, 2012
I'm trying to close a workbook without saving it. But everytime I get the message box popupped twice.
These are the codes that I use:
VB:
Private Sub Workbook_BeforeClose _(Cancel As Boolean)
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[Code].....
View 4 Replies
View Related
Jan 25, 2008
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
View 9 Replies
View Related
Nov 28, 2008
Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
View 3 Replies
View Related
May 25, 2007
I have created a workbook for a quality check and it has about 50+ worksheets in the workbook, which contains data, formula,graphic & vba function. The size of the file is about 4890kb. It's works as planned, except, it takes about 1 mins ++ to load, save & even exit. Is there any suggestion to eliminate this issue, apart from creating a few workbook to accomodate the number of sheets/item in the project?
View 2 Replies
View Related
Jul 23, 2014
I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.
When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".
View 4 Replies
View Related
Jan 11, 2007
I am trying to write a macro/program that will save a csv activeworkbook in xls and close it.
The workbook will always be a different name.
The files do not have an extension upon download and formatting.
ie stm0107 (Then they need to be saved as xls. Which is done manually at this time.)
Below are some trial and error attemtps. Thank you for your suggestions.
Sub CloseWorkbook()
Dim Filename As String
'Filename = Workbook.Name
MsgBox (ActiveWorkbook.Name)
Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename = ActiveWorkbook.Name(".xls")
'Filechange = Replace(Filename, "", "xls")
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub
View 9 Replies
View Related
May 15, 2014
Whenever I save and close the attached, after selecting the NO on line 23, I lose all the buttons associated with my macros. If I save with everything expanded, no rows hidden, my buttons stay put.
View 1 Replies
View Related
Sep 14, 2006
I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies
View Related
Aug 1, 2014
I have used the following code to lock cells when saving.
I need to exclude Cell B2 when saving
let me know what changes i should make.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"
[code]....
View 2 Replies
View Related
Mar 18, 2007
I am trying to lock any cell within a set range that contains data but I want this only to be activated when the user saves the file.
View 3 Replies
View Related
Mar 14, 2007
I am using the code below to prompt the user to enter his/her name in cell B4. What I would like to do is have it so that after the user enters his/her name, that cell is locked and cannot be cleared.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4" Then
Range("B4").Value = InputBox("Please Enter " + Range("a4"), "Company")
End If
End Sub
View 9 Replies
View Related
Aug 30, 2012
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
View 9 Replies
View Related
Oct 10, 2012
I've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
View 1 Replies
View Related
Aug 27, 2012
I'm creating an inventory of cars for a car dealership that has about 27 or so columns and 500 rows for entries. Currently there are 50 rows used to keep track of inventory and with the additional rows for future entries. I want to set it up where the data entered into the cells gets locked automatically when saved and reopened for future use and can only be altered if a password is entered. Table is C3:AE503.
View 5 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
View 1 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
View 1 Replies
View Related
Jul 31, 2012
I am attaching the example where I need to lock H2 to H1000, note that sheet is locked "anixter12 and I have also given range to user for modification I simply want to lock cells in H once date is entered.
View 3 Replies
View Related
Aug 13, 2013
I need the attached spreadsheet to lock cells after data is entered into cells E13-G74 and the page saved. It will be opened twice daily and saved. Also im not sure if it makes a difference but I would like all other cells outside this range locked except for C5, C6, C7 AND C8. I have seen several threads on the topic but am extremely new to using VBA and cannot make anything work.
View 6 Replies
View Related
Oct 16, 2009
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
View 9 Replies
View Related
Feb 28, 2007
How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.
View 5 Replies
View Related
Mar 30, 2007
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View 4 Replies
View Related
Apr 25, 2014
I have several data val drop down lists, that all have the same input, but for some reason, the first entry in the list will not save to the worksheet. When chosen, after being saved and closed out, the entry disappears when the document is re-opened.
Furthermore, the drop down lists that are linked to another cell WILL save the first entry when chosen.
Is this a programming issue?
View 1 Replies
View Related
Aug 20, 2013
How to set up an excel sheet to automatically save data after every data entry? Or maybe after a specified time period?
View 1 Replies
View Related
Aug 12, 2009
I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
View 2 Replies
View Related
Aug 22, 2012
I have a perfectly functioning piece of code. There is still one snag, however. In the process of running the macro, three new workbooks are opened. I would like to close all three of those workbooks at the end. I am using the following procedure for each of the sheets:
To open the worksheet
Code:
Dim wb As WorkbookMsgBox "Please select the Stakeholder File"SHFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Please select the Stakeholder Workbook")If SHFile "False" Then Set wb = Workbooks.Open(Filename:=SHFile)
To close the worksheet at the end:
Code:
If Not wb is Nothing Then wb.Close SaveChanges:=False
The only problem is that I am getting bugs when closing multiple workbooks (3). I have assigned the names wb, vb and ab to the three workbooks.
View 5 Replies
View Related
Apr 19, 2006
I have some VBA code which opens another workbook, copies data from the first sheet in there and pastes into the current sheet before closing the workbook it got the data from. Is there any way of suppressing any messages associated with closing the other workbook? i.e. do you want to save - or possibly automatically generating a no message to the prompts?
View 3 Replies
View Related
Jan 11, 2007
I have 4 employees who at the end of their shifts have to email me an excel workbook. The issue is that some of them are forgetful and after placing numerous post-it notes on their monitors one particular employees just forgets.
What i want to do if possible is that when the workbook gets closed, a pop-up window shows up on their screen reminding them to send the workbook. Even better would be if their was a button that they could click that would email the workbook as an attachment in the pop-up box.
View 11 Replies
View Related
Mar 25, 2014
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
View 2 Replies
View Related
Dec 6, 2007
Is it possible to pop up a message when closing the workbook?
if so, could the message only popup if a certain cell is negative?
For example, This is what i would like to happen.
When workbook close is activated:
If cell A1=(-20), then pop-up "Please check your work and correct".
else, close workbook
View 14 Replies
View Related
