473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Input Box data

Does anyone know how to do advanced input box functions? I am trying to use 1 input box to capture the users requests for several ID numbers. I then take these ID's and compare them to an Excel column and hide all the things that don't match these ID's. It works perfectly for a single entry. Not sure how to do multiple entries.

Here is my code.
Expand|Select|Wrap|Line Numbers
  1. Sub Enrollments()
  2. Dim rownumber As Integer, intLastRow As Integer, mycell As Integer, mycolor As Integer, startcell As Integer, X As Integer, Y As Integer, Z As Integer, A As Integer, myvar As Integer, columnnumber As Integer
  3.  
  4. Range("C1").Select
  5. intLastRow = ActiveCell.CurrentRegion.Rows.Count
  6. x1 = ActiveWorkbook.Name
  7.  
  8. Y = 6
  9. Z = 4
  10. A = 5
  11. startcell = 2
  12. myvar = 2
  13. columnnumber = 8
  14. rownumber = 2
  15.  
  16.  Columns("G:G").Select
  17.     Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
  18.         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  19.         Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  20.         :=Array(1, 1), TrailingMinusNumbers:=True
  21.  
  22. Columns(columnnumber).Select
  23. Selection.Insert shift:=xlToRight
  24. X = InputBox("Please enter the BPL numbers.", "BPL InputBox ")
  25. Cells(myvar, columnnumber).Select
  26. intLastRow = intLastRow + 1
  27.  
  28. Do Until myvar = intLastRow
  29. ActiveCell.FormulaR1C1 = "=if(RC[-1]=" & X & ",""MATCH"",""NO MATCH"")"
  30. If ActiveCell <> "MATCH" Then
  31. Rows(rownumber).Select
  32. Selection.EntireRow.Hidden = True
  33. End If
  34. ActiveCell.Offset(1, 0).Select
  35. If myvar = intLastRow Then
  36.   End If
  37. myvar = myvar + 1
  38. rownumber = rownumber + 1
  39. Cells(myvar, columnnumber).Select
  40. Loop
  41.  
  42. columnnumber = 16
  43. myvar = 2
  44. Cells(myvar, columnnumber).Select
  45. ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-12],RC[-11],RC[-9])"
  46. Range("P2").Select
  47. Selection.AutoFill Destination:=Range("P2", "P" & intLastRow)
  48. Cells(myvar, columnnumber + 1).Select
  49.  
  50. 'Counting for duplicates
  51. ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1]:R[" & intLastRow & "]C[-1],RC[-1])"
  52. Range("Q2").Select
  53. Selection.AutoFill Destination:=Range("Q2", "Q" & intLastRow)
  54. Cells(myvar, columnnumber + 1).Select
  55.  
  56. myvar = 2
  57. Cells(myvar, columnnumber + 1).Select
  58. Do Until myvar = intLastRow
  59. If ActiveCell <> 1 Then
  60. Selection.Interior.ColorIndex = 3
  61. End If
  62. ActiveCell.Offset(1, 0).Select
  63. If myvar = intLastRow Then
  64. End If
  65. myvar = myvar + 1
  66. Loop
  67.  
  68. myCount = 0
  69. mycolor = 3
  70. For I = startcell To intLastRow
  71.     mycell = Cells(I, columnnumber + 1).Interior.ColorIndex
  72.     If mycell = mycolor Then
  73.         myCount = 1 + myCount
  74.     End If
  75. Next I
  76. Worksheets("Export Worksheet").Cells(intLastRow, columnnumber + 1).Value = myCount
  77. Worksheets("Export Worksheet").Cells(intLastRow, columnnumber).Select
  78.  
  79. MsgBox "All Checks have been completed.", vbInformation, "Enrollments"
  80. End Sub
Sep 11 '09 #1
4 2663
smartchap
236 100+
Not gone through ur program but it seems that u want to enter multiple IDs in InputBox and do calculations, etc on individual ID. Here are my suggestion:
You can get multiple IDs in InputBox separated by a comma (,). Suppose u get:
Expand|Select|Wrap|Line Numbers
  1. a=InputBox("Please enter IDs separated by comma")
  2.  
Now use another variable, say 'ID', in which u separate these ID from a one by one using Instr function (inside a loop) and do ur calculation, etc for each ID.
Sep 15 '09 #2
Thanks very much for your help. I will try this and see if it works.
Sep 15 '09 #3
kadghar
1,295 Expert 1GB
@smartchap
Or using the SPLIT function, of course ^.^
Sep 18 '09 #4
smartchap
236 100+
Yes using Instr function will require aloop & a little bit of code inside loop. With Split function it is straight forward. I agree with kadghar. Posting ur query to such forums is beneficial that u get a lot of solutions & u can choose the best among them.
Sep 19 '09 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: SophistiCat | last post by:
Hi, I am working on a computational program that has to read a number of parameters (~50) from an input file. The program contains a single class hierarchy with about a dozen member-classes or...
7
by: MM | last post by:
Hi there, How can I change my code (below) so that I use an "input argument" to specify the file name of the input file? For example, if I compile the code and that the application then gets the...
7
by: F. Michael Miller | last post by:
I have a db with Access front end, sql back, linked tables. I need to be able to change input masks at the table level in code. Any ideas? Thanks!
7
by: Don Riesbeck Jr. | last post by:
I'm working on an application (OEM) using C# that utilizes input from a keyboard, and USB Barcode Scanner. The scanner is a HID Keyboard device, and input from it is sent to the system as if it...
8
by: FS Liu | last post by:
Hi, I am writing ATL Service application (XML Web service) in VS.NET C++. Are there any sample programs that accept XML as input and XML as output in the web service? Thank you very much.
15
by: Nathan | last post by:
I have an aspx page with a data grid, some textboxes, and an update button. This page also has one html input element with type=file (not inside the data grid and runat=server). The update...
3
by: aurora | last post by:
This is an entry I just added to ASPN. It is a somewhat novel technique I have employed quite successfully in my code. I repost it here for more explosure and discussions. ...
1
by: Iaintnofool | last post by:
Hi I'm stuck on a project using VB 2005 express edition. I'm having trouble reading in data into my code from a Text File. I'm using an openFileDialog so the user can select a file to open. The...
3
by: manxie | last post by:
Dear All Readers, I'm supposed to create a program with a switch and using voids to execute number of codes, that includes finding sum, average, maximum, and minimum, please read my code:...
1
by: moveitho | last post by:
Hi, I have the following script on my site but need to implement a select menu into the script as it would make it a lot tidier. This script adds the required amount of fields selected by the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.