Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Input
Below is a macro that was written because of a question I had in trying to
control cursor movement. This is provided for info. This macro works. I have three other questions at this time that may or may not need code written into this specific macro, but needs a macro nonetheless. I have been unable to find an answer on my own. The question involves two sheets, Orders & Items. The macro below is sheet specific to Orders. In the Orders sheet, a user will enter item numbers and quantities (Range A15:B90). Currently, within the Orders sheet, I match the Item number entered with the item number on the Item sheet. If there is no match, then I have a column (D15:D90) that indicates INVALID and the appropriate cells are conditional formatted to change to white font/red background. Q1) Since the spreadsheet itself cannot stop the progression of the cursor, I wouild like to perform this check within the macro and if no match is found, the cursor stays put until a correct item number is entered, according to the list in column A on the Item sheet. How can this check be performed in a macro? Q2) Is there any type of error routine that needs to be set up? Q3) I would like to have the cursor placed in cell B1 upon opening the workbook. How is that accomplished? CURRENT MACRO to control cursor movement in order form Private Sub Worksheet_Change(ByVal Target As Range) Dim Ans As Long If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub 'Entry in B4 If Target.Address(0, 0) = "B4" Then Ans = MsgBox("Ask something.", 4, "MsgBox Title here") Application.EnableEvents = False If Ans = vbYes Then Range("D2").Value = "Yes" Range("E7").Select Else Range("D2").Value = "No" Range("A15").Select End If Application.EnableEvents = True Exit Sub End If 'Entry in E9:G9 If Not Intersect(Target, Range("E9:G9")) Is Nothing Then Target.Offset(-2, 1).Select Exit Sub End If 'Entry in H9 If Target.Address(0, 0) = "H9" Then Range("A15").Select Exit Sub End If 'Entry in A15:B90 If Not Intersect(Target, Range("A15:B90")) Is Nothing Then If Target.Column = 1 Then Target.Offset(, 1).Select Else Target.Offset(1, -1).Select End If End If End Sub Thanks for your help, Les |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
verify user input | Excel Programming | |||
Verify user input box is a Month End Date | Excel Programming | |||
Verify information and input a value | Excel Discussion (Misc queries) | |||
Several Dates -Verify | Excel Worksheet Functions | |||
Verify names | Excel Programming |