PDA

View Full Version : want to compel user to enter data to both col B and then col C if data is in col A



jonnyboy
08-26-2013, 01:49 PM
I am a new user to VBA coding and this is a whole brave-new world. I am creating a time sheet which has a great deal of time allocations based upon "validated" dept./type codes in columns B and C. What I am looking to accomplish, is to be sure that once the employee/user enters a description of worked performed in cell A, then I want the user to be forced to enter specific letter code (from validation lists) in both Column B, and Then also, in Col C. Here is what I have so far... I can only force entry into col B based on this VBA code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myC As Range
For Each myC In Range("A8:A24")
If myC.Value <> "" And myC(1, 2).Value = "" And Intersect(Target, myC.Resize(1, 2)) Is Nothing Then
Application.EnableEvents = False
myC(1, 2).Select
MsgBox "Enter code please"
Application.EnableEvents = True
Exit Sub
End If
Next myC
End Sub


How do I modify my VBA code to compel user to subsequently enter a code to col C before proceeding to next line?

Your expertise will be extremely appreciated.

Jon

mikerickson
08-26-2013, 03:06 PM
you might want to use an input box to get the entries rather than returning control to the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myC As Range
For Each myC In Range("A8:A24")
Select Case Len(myC.Value) * WorksheetFunction.CountA(myC.Resize(1, 3))
Case 1, 2
Application.EnableEvents = False
myC.Offset(0, 1).Value = InputBox("enter column B")
myC.Offset(0, 2).Value = InputBox("enter column C")
Application.EnableEvents = True
End Select

Next myC
End Sub

jonnyboy
08-26-2013, 04:33 PM
I do see what the input box is now. Thankyou for that However I am still wanting it to have the cursor on the next highlighted cell (b or c) awaiting input of a list validated code before proceeding to next cell input. The input box popping up is not quite the effect I was after as it was allowing me to enter anything to the input box rather than the validation list possible letter codes.

I had it doing exactly what I wanted in Col B with the original code and am mostly interested in the code duplicating the process into the next cell (same row, Col C)

The flow I am after:

"If anything at all is typed into column A, then insist on entry to Col B (code f,h,s,or a) before proceeding to Col C and then insisting on entry in cell C (code A or D)"

mikerickson
08-26-2013, 11:35 PM
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
If Not Application.Intersect(Target, Range("A8:C24")) Is Nothing Then
For Each oneCell In Application.Intersect(Target, Range("A8:C24"))
With oneCell.EntireRow.Range("A1")
If (.Value <> vbNullString) And (WorksheetFunction.CountA(oneCell.EntireRow.Resize(1, 3)) <> 3) Then
If .Offset(0, 1) = vbNullString Then
.Offset(0, 1).Select
Else
.Offset(0, 2).Select
End If
MsgBox "Fill the cell"
Exit Sub
End If
End With
Next oneCell
End If
End Sub