MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merging to input boxes


Posted by Rich Baur on February 06, 2001 1:01 PM

How can I combine these two input boxes. The first input box takes a 15 digit number and places it in the active cell. The second input box takes a 125 digit number and breaks it down into 5 15 digit numbers. It then places the 5 15 digit numbers into the active cell and then the next 4 cells. I want to have just 1 input box that can do this.

1)Sub IMEI()
'Input Box for single bar codes
Do
Application.OnKey "s", "IMEI"
' Runs aoutmatically by pressing z

EIMEI = Application.InputBox("Enter the IMEI", , , , , , 1 + 2)
' Code for input box

If EIMEI = "" Then Exit Sub
' On OK if Scan iz zero then you will exit the input box

ActiveCell.Value = EIMEI
' Enters IMEI into the Active cell
ActiveCell.Offset(1, 0).Range("a1").Select
' Moves active cell down one cell

Loop
End Sub

2)Sub BUBBLE()
'Input Box for Bubble bar codes
Do
Application.OnKey "b", "BUBBLE"
' Runs aoutmatically by pressing b

Dim EBUBBLE As String
EBUBBLE = Application.InputBox("Scan the Bubble", , , , , , 1 + 2)
' Code for input box

If EBUBBLE = "" Then Exit Sub
'On OK if Scan iz zero then you will exit the input box


ActiveCell.Value = Mid(EBUBBLE, 14, 12)
ActiveCell.Offset(1, 0).Range("a1").Select
' Selects first IMEI
ActiveCell.Value = Mid(EBUBBLE, 39, 12)
ActiveCell.Offset(1, 0).Range("a1").Select
' Selects second IMEI
ActiveCell.Value = Mid(EBUBBLE, 63, 13)
ActiveCell.Offset(1, 0).Range("a1").Select
' Selects third IMEI
ActiveCell.Value = Mid(EBUBBLE, 88, 13)
ActiveCell.Offset(1, 0).Range("a1").Select
' Selects fourth IMEI
ActiveCell.Value = Mid(EBUBBLE, 113, 13)
ActiveCell.Offset(1, 0).Range("a1").Select
' Selects fifth IMEI


Loop
End Sub



Posted by Dave Hawley on February 06, 2001 1:18 PM

Hi Rich

Using just the one inputbox and having a 140 digit number, you maybe able to use:

Num1 = CLng(Left(EIMEI, 15))
Num2 = CLng(Right(EIMEI, 125))


Any Good ?

OzGrid Business Applications

Posted by Christine on February 09, 2001 2:11 AM

Grouping data and counting

I have spreadsheet of 4 columns that have assored dates in the 1st column. I wnat the user to input a date eg. 01/01/2001 to 31/01/2001 and all records across the 4 columns would be selected for that date period only - then I want to count the items in the for columns eg. like apples = 4. So the end result would be Between the period 01/01/2001 to 31/01/2001 - there was 4 apples.

Can anyone help!!