Hi,
Your problems are typical of problems that occur with 'Protected Worksheets'. The 1004 error is probably occurring because you are trying to change the contents of a 'Locked' cell on a 'Protected' Worksheet. Since you are using a UserForm, you probably need to add a couple of lines in the 'Ammend' and 'Add new record' CommandButton Event handler code to unprotect the sheet while writing to the Spreadsheet. For example:
my original timestamp (in Column B) disappears completely... but Column G the employee number column, ends up displaying what appears to be a date, but with the date and time 1 day prior.
Without a sample workbook, I can only suggest methods to diagnose your problems. Obviously, you are probably writing BLANK data to Column 'B' and writing something to Column 'G' both of which you don't want.
I suggest you do the following:
a. Before trying to diagnose the problem, make sure you have the correct code to write to Column 'C'. Write a test macro something like the following to make sure you have code that does what you want:
b. Next, find where you are going wrong. To start with, it is a lot easier to debug UserForm code when the UserForm is Modeless which allows access to Excel Resources. When you open the UserForm use code like:
When your code is finished you can remove the vbModeless to make the Userform Modal, which locks out Excel Resources.
c. Put a breakpoint at the beginning of the UserForm 'Ammend' and/or 'Add new record' CommandButton Event handler code and single step through the code. Each time you single step take a look at the spreadsheet to make sure the action you thought you requested, actually occurred.
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.
To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm
To upload a sample workbook: Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Bookmarks