Mumps.
To create the input form to have VBA input it to a datasheet, I have, I followed a TeachExcel.com tutorial on Youtube. The way the instructor demonstrated how the code should be written was to do all the data-validation on the excel sheet, and then call a different module (Call_Store_update_Data).
There are Several modules to the sheet.
One that validates the data.
One that stores the data on a separate data-sheet (Database)
One that Searches for existing data to be able to change it
One that either stores or updates - new stores on next empty row, or updates in the position the data was found.
Therefore, unless I am not sure at which point in the VBA modules I should paste the code you suggested.
My understanding is that all the data-validation is presently done on the input sheet first.
To better describe what I am talking about,, I will show the formulas in each of the cells in columns I, J, L and M
(M does not have coding - only a 0 or a 1 - by default set to 0)
Cell I4 - =AND(LEN(F4)>3,ISNUMBER(F4))
Cell I5 - =AND(LEN(F5)>0,LEN(F5)<50)
CELL I6 - =LEN(F6)>0 - Also has date Data Validation set up in F6 as allow Data - Data Grater than - Start Date 01/01/1950 (Any date below 1st Jan 1950 not valid)
CELL I7 - =LEN(F7)>0 - Drop down Data Validation list set up for F7. The drop down list - does not refer to cells, but have selected Data Validation list and allowed valid values of 1,2,3,4
CELL I8 - =LEN(F8)>0 - Also Drop down list in F8 which refers to List sheet Cells C2 to C7 (Crew, Services etc)
CELL I9 - =AND(LEN(F9)>0,LEN(F9)<50) - At the Moment I have a drop-down in cell F9 referring to the "List" Sheet cells A2 to A28 (Crew job titles), but I only want this to be referred to if "Crew" is selected in F8
CELL I10 - =LEN(F10)>0 - Data validation in Cell F10 set to Date Greater than 11/06/21 (periodically change date as time passes)
CELL I11 - =LEN(F11)>0 - Data Validation in Cell F11 - Same parameters as F10
CELL I12 - =LEN(F12)>0 - Data Validation in cell F12 set to greater than a recent date (11/06/21) - periodically changed.
CELL I13 - =LEN(F13)=4 - Data Validation in cell F13 refers to "List Sheet" cells G2 to G149 (Table name = Cabins_Table)
CELL I14 - = =LEN(F14)>0 - Dropdown list in cell F14 - does not refer to cells, but valid values listed as 1,2, 3
CELL I15 - =AND(LEN(F15)>0,LEN(F15)<50)
CELL I16 - =LEN(F16)>0 Data Validation drop-down in cell F16 - does not refer to cells but list offers options as D, N, UNK
CELL F17 - =AND(LEN(F17)>1,LEN(F17)<4)
The only remaining cell which has any sort of validation is a drop-down in cell F20 which allows either a 1 or a blank to be entered. Cells F18, F22, and F24 do not require any validation.
Column J (formatting) is used to conditionally format the cells in Column F if the validation in Column I returns "False"
Cell J4 reads as follows =AND(NOT(I4),$L$4) That formula is pulled down to cell F17
cell L4 is a cell to show the number of errors. Formula as follows:
Referred to in VBA module as Errorcount: - (Sub validate-Form() refers to Errorcount - Range (L4").Value
Cell M4 (Show Error) is set to 0
reference on vba Sub validate_form reads:
Set showErrorCell = Range (M4)
When it checks for errors
IF errorcount >0 Then
MsgBox errorcount & " Error(s)"
ShowErrorcell.value = 1
If there are no errors, it Submits data to the table and ShowErrorcell.value = 0
In a nutshell, as far as I can see, all the data validation is done on the sheet I have described above, which leads me to question where I would paste the VBA you sent me.
some Validation I will need, that I have not yet forced is to ensure that I enter data in Cell F8 before I enter data in F9. I thought I would figure out how to solve the problem of only having a drop-down list in F9 if Cell F8 = "Crew".
There are 4 VBA modules that link together to make the program work. which are copied and pasted below.
1st Module..................
Sub validate_form()
errorcount = Range("L4").Value
Set showErrorCell = Range("M4")
'unprotect the worksheet
ActiveSheet.Unprotect "123456"
'check for errors.
If errorcount > 0 Then
'Error!
' tell the user how many errors there are.
MsgBox errorcount & " Error(s)"
'Allow conditional formatting for errors to be displayed.
showErrorCell.Value = 1
Else
'NO Error - All Good
'Store the Data
' - call the macro form that does that.
Call Store_update_Data
'Success - Do something!
MsgBox "Data Submitted to Table!"
'Turn conditional formatting for erros off.
showErrorCell.Value = 0
End If
'Protect the Worksheet
ActiveSheet.Protect "123456"
End Sub
2nd Module.................
Sub Store_Data()
'Take Data from one worksheet and store it in the next empty row on another worksheet
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")
nextRow = dataSheet.Range("C" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 15).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F10").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("F12").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("F14").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("F16").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 16).Value = sourceSheet.Range("F18").Value
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F20").Value
dataSheet.Cells(nextRow, 17).Value = sourceSheet.Range("F22").Value
dataSheet.Cells(nextRow, 18).Value = sourceSheet.Range("F24").Value
'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""
End Sub
3rd Module........
Sub Select_Data()
'Search the data repository worksheet and return the found record into the form
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim SearchValue As Variant
Dim DataIdCol As Range
Dim recordRow As Integer
'Make some sheet variables so that we canuse those instead of hard-coding
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")
'Column that contains the value for which we will search.
Set DataIdCol = dataSheet.Range("B2:B2000")
'Value to search for.
SearchValue = InputBox("Input Vantage number)", "Search by Vantage Number")
'Check if the user inputs a value and hit the ok button
If SearchValue <> vbNullString Then
'Value input, so search for it.
'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""
'Search
'More specific information:
Range.Find method (Excel)
Set Rng = DataIdCol.Find(What:=SearchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
'check if a record was found.
If Not Rng Is Nothing Then
' Record Found!
'Get the row of the record.
recordRow = Rng.Row
'Put the records information back into the form.
sourceSheet.Range("F4").Value = dataSheet.Cells(recordRow, 2).Value
sourceSheet.Range("F5").Value = dataSheet.Cells(recordRow, 3).Value
sourceSheet.Range("F6").Value = dataSheet.Cells(recordRow, 4).Value
sourceSheet.Range("F7").Value = dataSheet.Cells(recordRow, 5).Value
sourceSheet.Range("F8").Value = dataSheet.Cells(recordRow, 15).Value
sourceSheet.Range("F9").Value = dataSheet.Cells(recordRow, 7).Value
sourceSheet.Range("F10").Value = dataSheet.Cells(recordRow, 8).Value
sourceSheet.Range("F11").Value = dataSheet.Cells(recordRow, 9).Value
sourceSheet.Range("F12").Value = dataSheet.Cells(recordRow, 10).Value
sourceSheet.Range("F13").Value = dataSheet.Cells(recordRow, 11).Value
sourceSheet.Range("F14").Value = dataSheet.Cells(recordRow, 12).Value
sourceSheet.Range("F15").Value = dataSheet.Cells(recordRow, 13).Value
sourceSheet.Range("F16").Value = dataSheet.Cells(recordRow, 14).Value
sourceSheet.Range("F17").Value = dataSheet.Cells(recordRow, 6).Value
sourceSheet.Range("F18").Value = dataSheet.Cells(recordRow, 16).Value
sourceSheet.Range("F20").Value = dataSheet.Cells(recordRow, 1).Value
sourceSheet.Range("F22").Value = dataSheet.Cells(recordRow, 17).Value
sourceSheet.Range("F24").Value = dataSheet.Cells(recordRow, 18).Value
Else
'Nothing found, tell the user
MsgBox "Record not found."
End If
End If
End Sub
4th Module
Sub Store_update_Data()
'TeachExcel.com
'Takes data from one worksheet and stores it in the next empty row on another worksheet
'or, if the record exists already, it will be updated.
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Database")
'Column that contains the value for which we will search.
Set DataIdCol = dataSheet.Range("B2:B2000")
'Value to search for.
SearchValue = sourceSheet.Range("F4").Value
'Check if the user inputs a value and hit the ok button
If SearchValue <> vbNullString Then
'Value input, so search for it.
'Search
'More specific information:
Range.Find method (Excel)
Set Rng = DataIdCol.Find(What:=SearchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
'check if a record was found.
If Not Rng Is Nothing Then
' Record Found!
'Get the row of the record.
nextRow = Rng.Row
Else
'Input new record!
'Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("C" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
End If
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 15).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F10").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("F12").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("F14").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("F16").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 16).Value = sourceSheet.Range("F18").Value
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F20").Value
dataSheet.Cells(nextRow, 17).Value = sourceSheet.Range("F22").Value
dataSheet.Cells(nextRow, 18).Value = sourceSheet.Range("F24").Value
'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F10").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F12").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F14").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F16").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F18").Value = ""
sourceSheet.Range("F20").Value = ""
sourceSheet.Range("F22").Value = ""
sourceSheet.Range("F24").Value = ""
End If
End Sub
My apologies if I have missed anything, I've not had as much time as I would have liked to go through everything, but I have tried to be thorough with the information I am providing.