Required fields, validate on save

[enuf2b]

Hi,
I am very new to vba, and don't know enough to even try this: here is the goal. In a multi-sheet/tab Excel workbook template, where each sheet requires information to be entered in pre-defined column fields, some with pull-downs, some not. T; so the first requirement is making cells required so they aren't left blank [incomplete] (this is server info, so must include server name, IPs VLANS, ports, environment, apps, etc.) the tricky part is I can't pre-define a range of required cells beacuse it will differ i.e. some projects will have 20 servers, others 200. So I need it to have some sort of if...then..type parameter if the Server Name field (1st field) is entered, then everything right of that on that row must be completed.
Then, as each worksheet is "saved" it validates that there are no missing data fields. A great thing would be to tell the user what they missed! or color-code it. The worksheet is then passed to the next group, who completes their worksheet tab, same requirements as above (required fields, validate on save) pass it on, etc.

Can it be done? How hard is this?

[Bob Phillips]

It is pretty straight-forward. You would use the BeforeSave workbook event to check all of the fields, and add colour where appropriate, not saving if any incompletes.

Something like

[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LastRow As Long
Dim vecColours As Variant
Dim fErrors As Boolean
Dim i As Long
Dim j As Long

veColours = Array(xlColorIndexNone, 25, 28, 39, 40, 37) 'different colours for different columns
With Me.Worksheets("Master")

'handle dynamic number of projects
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'column A is server name
For i = 2 To LastRow

'clear it down first
.Range("A2").Resize(LastRow, 5).Interior.ColorIndex = xlcolorindexnon

'assume 5 columns of data
For j = 2 To 5

If .Cells(i, j).Value = "" Then

.Cells(i, j).Interior.ColorIndex = vecColours(j - (LBound(vecColours) + 1))
fErrors = True
End If
Next j
Next i

If fErrors Then

MsgBox "You have invalidate input"
Cancel = True
End If
End With

End Sub
[/vba]

To be more helpful, we would need more details.

[enuf2b]

Thanks xld! Let me give this a try and let you know. Again as a newbie this is all trial and error for me :-)
What other details could help you (help me)

enuf2b

[Bob Phillips]

The names of the worksheets, what data is mandatory in each row, which columns that they occupy.

Imagine you were asking someone to build it for you, what would they need to know.

[enuf2b]

Gotcha. Ok.

So the workbook is a template and is comprised of 9 worksheets. Its 1 workbook per project and a project can have 2 servers or 200+.
Tab 1 contains instructions to the users. Who is to fill out what and where.
Tab 2 : Server_provisioning tab (Range A-AF) Column AF (comments column) not required. The following required columns have pull-down selections to chose from (B,C,J,K,L,M,N,O,P,Q,R and S)
The server engineer will enter data from columns A through X only.
Server Name(A),Environment(B),Network Pod(C),IP Address Front(D),VLAN Front(E),IP Address Back(F)VLAN Back(G),IP Address Offnet(H),VLAN Offnet(I),Location(J),Model(K),Processor(L),Memory(M),Drives(N),Partition Carve (O),OS(P),Description(Q),SAN Attached?(R),Clustered?(S),Heartbeat (in IP format e.g. 192.168.x.y)(T),Application ID(U),Application Name(V),Application Owner(W),Application Owner Contact Number(X).
Then Saves and passes it to the Provisioning Eng.
Tab 2 - Server_Provisioning
The provisioning engineer will enter data from columns Y through AF only
DataCtr Floor Location (Y),Rack Location(Z),ILO Password(AA),Serial Number(AB) WWN(AC),Network Ports(AD),Storage Ports(AE), Comments(AF)
Tab 3: Infrastructure Cabling & SAN - Columns A-G - all populated from Tab 2 entries, except comments column G.
The following columns are autopopulated (from Tab 2 entries): server name, data center floor location, rack location, fabric switch, & fabric switch port for both A and B Fabrics.
Tab 4: Infrastructure Cabling & Network
The following columns are autopopulated: server name, data center floor location, rack location, fabric switch, & fabric switch port for both A and B Fabrics.
Tab 5: SAN Storage
The following columns are autopopulated: A-D) server name, rack location, WWN, & clustered. The SAN Storage engineer should complete the following fieldsE-K)
Fabric Switch A, Fabric Switch Port A, # of LUN, Tier, and Size

Tab 6: Firewall
The following columns (A-D) are autopopulated: source server name, environment, IP address back, VLAN back, The server engineer should complete the following fields: (E-I) Source IP address, Destination Server Name, Destination IP Address, URL/Domain Name, TCP/UDP Ports
Tab 7: Load Balancer
The server engineer is responsible for completing all of the columns associated with the Load Balancer tab.
Environment(A),DNS Name(B) VLAN(C)Network Layer(D),IP(E) Destination Servername 1(F),Destination IP 1(G),Destination Servername 2(H),Destination IP 2(I),Destination Servername 3(J),Destination IP 3(K),Destination Servername 4(L),Destination IP 4(M),Comments(N)
Tab 8: Monitoring
The following columns(A-G) are autopopulated: server name, environment, OS, application ID, application name, application owner, & application owner contact number.
The server engineer completes the custom rules (H),agent installed(I) and Monitoringh active? (J) fields.
H-J - and these have pull-down options.
The monitoring engineer confirms that the monitoring was installed and is active by completing the "Monitoring Active" field and closes the work order.
Tab 9: Back-up
The server engineer is responsible for completing all of the columns associated with the Back-up tab. Note: This tab is unique. I would probably need to re-work it or send it for you to see. The server engineer generates a work order to the back-up team for completion of this phase of the design document.
Much appreciated!

[enuf2b]

Did I do something wrong? Any help here?

enuf2b

[mdmackillop]

Please post a sample workbook if you need further assistance. (Manage Attachments in Go Advanced reply section)

[enuf2b]

Attached is the worksheet I am working with Instructions are on wksheet 1, then as stated previously, gets passed around to complete. But we need to find a way to insure the fields are completed before passing on

any assistance is greatly appreciated!
enuf2b