Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
Hey all
I'm sure this question has been asked a few times before ;o). Basically I'm looking for a validation rule for the below. I have a row of data, say A1,B1,C1,D1 where a1 can have either value or used or Inuse. When the user selects 'Unused' from the dropdown, they will be forced to enter in details in to the other cells. I.e Name, Department, Cost Center ect... I've tried an IF statement but not being technical this has me stumped. Any help provided would be a godsend. Much thanks! Mel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
try explain better, i dont get it :-)
"Mel1221" skrev: Hey all I'm sure this question has been asked a few times before ;o). Basically I'm looking for a validation rule for the below. I have a row of data, say A1,B1,C1,D1 where a1 can have either value or used or Inuse. When the user selects 'Unused' from the dropdown, they will be forced to enter in details in to the other cells. I.e Name, Department, Cost Center ect... I've tried an IF statement but not being technical this has me stumped. Any help provided would be a godsend. Much thanks! Mel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
sorry bout that...my brain is fried...here goes.
When entering a value into a cell, A1 for example, I would like to force the user to enter data into B1,C1 aswell. In this example the user would enter 'Yes' in A1, and then be forced to enter in their first name in B1 and last name in C1 before exiting out of the spreadsheet. Hope this is clearer! excelent wrote: try explain better, i dont get it :-) "Mel1221" skrev: Hey all [quoted text clipped - 11 lines] Much thanks! Mel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
Mel
To "force" a user to enter data in cells requires VBA code. Either in the Before_Close or Before_Save subs or in a worksheet event code. Of course, this then requires that the users enable macros in order for the code to work. Which leads to more code that shows only a message sheet and hides the others if the users disable macros. The message would read similar to .."This workbook is non-usable unless you re-open it with macros enabled". The question now becomes.............How much effort and learning are you willing to invest in this project? Post back if you really need this. We can try to work you through it. Gord Dibben MS Excel MVP On Mon, 03 Jul 2006 09:04:36 GMT, "Mel1221 via OfficeKB.com" <u23678@uwe wrote: sorry bout that...my brain is fried...here goes. When entering a value into a cell, A1 for example, I would like to force the user to enter data into B1,C1 aswell. In this example the user would enter 'Yes' in A1, and then be forced to enter in their first name in B1 and last name in C1 before exiting out of the spreadsheet. Hope this is clearer! excelent wrote: try explain better, i dont get it :-) "Mel1221" skrev: Hey all [quoted text clipped - 11 lines] Much thanks! Mel |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
Thanks for the response, I need it enough that I'm willing to put in the
effort if you are :o) Where should I start? Instead of having the code in the Before_Save sub could it be triggered when the user changes a particular value? i.e When user selects the value "used" from a dropdown in say A1 Then A2 cannot be blank, and A3 cannot be blank....? Although I'm not too sure how to code this into the worksheet? Again, hope this makes sense! Cheers, Mel Gord Dibben wrote: Mel To "force" a user to enter data in cells requires VBA code. Either in the Before_Close or Before_Save subs or in a worksheet event code. Of course, this then requires that the users enable macros in order for the code to work. Which leads to more code that shows only a message sheet and hides the others if the users disable macros. The message would read similar to .."This workbook is non-usable unless you re-open it with macros enabled". The question now becomes.............How much effort and learning are you willing to invest in this project? Post back if you really need this. We can try to work you through it. Gord Dibben MS Excel MVP sorry bout that...my brain is fried...here goes. [quoted text clipped - 16 lines] Much thanks! Mel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mandatory Cells
Mel
I would place code in the Before_Save event in Thisworkbook Module Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Sheets("Sheet1").Range("A2,A3") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub If you want users to get a reminder before they try to save, stick this in the Sheet Module. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target If .Value = "used" Then MsgBox "Please fill in cells A2 and A3" End If End With CleanUp: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Wed, 05 Jul 2006 10:38:49 GMT, "Mel1221 via OfficeKB.com" <u23678@uwe wrote: Thanks for the response, I need it enough that I'm willing to put in the effort if you are :o) Where should I start? Instead of having the code in the Before_Save sub could it be triggered when the user changes a particular value? i.e When user selects the value "used" from a dropdown in say A1 Then A2 cannot be blank, and A3 cannot be blank....? Although I'm not too sure how to code this into the worksheet? Again, hope this makes sense! Cheers, Mel Gord Dibben wrote: Mel To "force" a user to enter data in cells requires VBA code. Either in the Before_Close or Before_Save subs or in a worksheet event code. Of course, this then requires that the users enable macros in order for the code to work. Which leads to more code that shows only a message sheet and hides the others if the users disable macros. The message would read similar to .."This workbook is non-usable unless you re-open it with macros enabled". The question now becomes.............How much effort and learning are you willing to invest in this project? Post back if you really need this. We can try to work you through it. Gord Dibben MS Excel MVP sorry bout that...my brain is fried...here goes. [quoted text clipped - 16 lines] Much thanks! Mel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Check/Tick box's and Mandatory cells | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |