I have a workbook with 116 Columns in Excel 2013. The first 38 Cols have strings or dates which the User can initialise a Userform to edit the existing strings or dates or can add new row of data to the sheet.
My problem, I have several columns with formula to calculate days between date entered and current date (today) and a countdown of days alongside in the next column. E.g. Col K is User input, Col L and M have formula pre-entered in the sheet.
My userform loop code (thanks to OzGrid) allows the User to obtain the entry to edit by selecting a unique row ID#. Works perfectly replacing / overwriting the current cell contents as required, but I need to "skip" the cells/columns containing formula when the User writes back to the sheet to leave the formula cells "untouched" by the module. There are 20 columns with the formula contained in each row cell of the respective column. The are not numerically sequential.
E.g Col K User Data, Col L Formula, Col M, then Col N User Data, Col O Formula, Col P Formula, Col Q User Data R Formula, Col S Formula and so on
I have tried including the formula with the VBA Userform code, without success, as the module uses uses textbox reference to get the date and replace base on Userform content (unless cancelled !)
I have tried hidden textboxes on the Userform to collect formula and cancel any change event for the respective textbox to cell value without success.
1. Is there a way with my self-taught/learning VBA "skills", to have VBA skip these columns within the loop control, or
2. Is there a way to exclude through a change event and leave the formula intact or
3. Can the loop be adjusted to skip or not overwrite the formulas (perhaps by using a range reference?)
Open to other suggestions in how to achieve this of course, being mindful my limited VBA knowledge - no doubt making life too complex and there is a sensible and easier solution available
Have placed relevant get and edit module code below for reference:
The first part looks up ID# User place sin Textbox 1 and populates Userform
Dim id As Integer, i As Integer, j As Integer, flag As Boolean Sub GetData() If IsNumeric(DataEditUserForm.TextBox1.Value) Then flag = False i = 0 id = DataEditUserForm.TextBox1.Value Do While Cells(i + 5, 1).Value <> "" If Cells(i + 5, 1).Value = id Then flag = True For j = 2 To 38 DataEditUserForm.Controls("TextBox" & j).Value = Cells(i + 5, j).Value Next j End If i = i + 1 Loop If flag = False Then For j = 2 To 38 DataEditUserForm.Controls("TextBox" & j).Value = "" Next j End If Else ClearForm End If End Sub
This part allows any editing / changes to be placed back in workbook (sheet 1) - including my formulas !
Sub EditAdd() 'Note to myself - Text boxes must be in sequential order ActiveSheet.Unprotect "password removed for this post" Dim emptyRow As Long If DataEditUserForm.TextBox1.Value <> "" Then flag = False i = 0 id = DataEditUserForm.TextBox1.Value emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 Do While Cells(i + 5, 1).Value <> "" If Cells(i + 5, 1).Value = id Then flag = True For j = 2 To 38 Cells(i + 5, j).Value = DataEditUserForm.Controls("TextBox" & j).Value Next j End If i = i + 1 Loop If flag = False Then For j = 2 To 38 Cells(emptyRow, j).Value = DataEditUserForm.Controls("TextBox" & j).Value Next j End If End If ActiveSheet.Protect "password removed for this post", True, True End Sub
Screen shot of workbook area of issues is hopefully attached also.
My extended thanks in advance,