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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Input
Based on your description of what you are trying to do, it seems that you
would want to use an If...Then...Else statement to check the range of items for a match and if no match is found, exit the sub which would leave the cursor in place. You could also use For...Each...Next in combination with an If staement to check the range without moving the cursor. The If statement would handle the condition of a match or no match. I can't help you with the code because I am not sure exactly what you are trying to do, just guessing. On Q3, You can use: Sub Workbook_Open() Worksheets(1).Range(B1).Select End Sub Put this in the ThisWorkbook code area instead of the general code module. "WLMPilot" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Input
I am unsure what the code you responded with below does. Basically this is
what is happening. Two sheets: Order & Items. Once the user gets into the range of A14:B90, item numbers and quantity to reorder is entered. A14:A90 is the item number and B14:B90 is the quantity. The macro that I included is there for information for you to see if needed. The macro's purpose in that range is to move the cursor from A14 to B14, then A15 to B15 (ie item number and quantity) instead of having a downward movement. Once an item number is entered, lets say in A14, I want to take that number and match it against a list on the Item sheet in column A. If there is not an exact match, ie item number does not exist, then I need a message box to pop up and state this fact and the cursor to remain in A14 until a match is made, ie correct item number is entered. Hope that helps, Les "JLGWhiz" wrote: Based on your description of what you are trying to do, it seems that you would want to use an If...Then...Else statement to check the range of items for a match and if no match is found, exit the sub which would leave the cursor in place. You could also use For...Each...Next in combination with an If staement to check the range without moving the cursor. The If statement would handle the condition of a match or no match. I can't help you with the code because I am not sure exactly what you are trying to do, just guessing. On Q3, You can use: Sub Workbook_Open() Worksheets(1).Range(B1).Select End Sub Put this in the ThisWorkbook code area instead of the general code module. "WLMPilot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Input
This will correct the code I gave you for putting the cursor in cell B1 when
the workbook opens. To use it, you press Alt+F11 to open the VB Edito, double click on ThisWorkbook in the Project window and paste this code in, save, then close: Sub Workbook_Open() Worksheets(1).Range("$B$1").Select End Sub I'll have to work on the code for the other a little. I think you are right about using the worksheet change, since you are trying to match an input to a cell. However, you could use the InputBox and put the code in the standard module rather than attach it to the worksheet. I'll take a look. "WLMPilot" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Input
You can play around with where you want to put this, but it will go from your
orders sheet (sheet two in the code) to your items sheet (sheet 3 in the code) find the first occurence of a match and pop up a message box that says either it found a match or didn't. The cursor on the orders sheet does not move while this happens. It's your toy now, so play with it as you will. Sub findItem() Dim srchRng As Range Set srchRng = Worksheets(3).Range("A15:A90") myItem = ActiveCell.Value With srchRng Set c = .Find(2, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address End If fndItem = Range(firstAddress).Value End With If fndItem = myItem Then MsgBox "Item Found" Else MsgBox "Not there" End If End Sub "WLMPilot" wrote: 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 |
Reply |
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 |