Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can i make a cell compulsory to complete?
I am trying to create a document for administrators to complete & want to
know if there is a way I can make a cell or cells compulsory to complete (data would be either free text or from a drop down list). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can i make a cell compulsory to complete?
A lot depends on what you mean by "complete" Are they entering new values into a row in a database, or a a set of cells that look like a form, or.... It is doable. For example, you could use the change and selection change events to force an entry in scolumn D based on a cell in column B being filled in. The code below will force an entry in column D (of the same row) for every cell filled in range B2:B10. To get the code to work, copy the code, right-click the sheet tab, select "View Code", and paste in the window that appears. Of course, the specific code required for your forced completion depends entirely on the logic and flow of the sheet. HTH, Bernie MS Excel MVP Dim ForceChange As Boolean Dim myR1 As Range Dim myR2 As Range Const myA1 As String = "B2:B10" Const myA2 As String = "D2:D10" Dim i As Integer Private Sub Worksheet_Change(ByVal Target As Range) Dim myNum As Long Set myR1 = Range(myA1) Set myR2 = Range(myA2) ForceChange = False If Intersect(Union(myR1, myR2), Target) Is Nothing Then Exit Sub For i = 1 To myR1.Cells.Count If myR1.Cells(i).Value < "" And myR2.Cells(i).Value = "" Then ForceChange = True Exit Sub End If Next i End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not ForceChange Then Exit Sub Set myR1 = Range(myA1) Set myR2 = Range(myA2) Application.EnableEvents = False For i = 1 To myR1.Cells.Count If myR1.Cells(i).Value < "" And myR2.Cells(i).Value = "" Then myR2.Cells(i).Select MsgBox "Please enter a value in cell " & _ myR2.Cells(i).Address(False, False) & Chr(10) & _ "You have a value in " & myR1.Cells(i).Address(False, False) _ & ", and you need one here." End If Next i Application.EnableEvents = True End Sub "mrh1973" wrote in message ... I am trying to create a document for administrators to complete & want to know if there is a way I can make a cell or cells compulsory to complete (data would be either free text or from a drop down list). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compulsory cells | Excel Discussion (Misc queries) | |||
How can I make certain fields compulsory if others are entered | Excel Discussion (Misc queries) | |||
compulsory/madatory cell | Excel Discussion (Misc queries) | |||
combining rows of data to make complete records | Excel Discussion (Misc queries) | |||
Need to make entire workbook of many pgs print as 1 complete bookl | Setting up and Configuration of Excel |