[RESOLVED] [Excel 2003] Excel 2003 coding VBA help needed

[Silverbreez]

Hi all,

I am developing a rudimentary, and what I intend to be a very simple inventory program to track my IT assets within my company, I won't have more than 500 items to potentially track so I'm trying to eliminate the need for a database program or some overly-complex system.

WHat I want to do is create a VB Form in Excel that has a text box, and when something is input into that text box (A simple value from a barcode scanner) the input is put in, and then searched for in my Spreadsheet and any values that I would like to display are displayed in corresponding text boxes, or I can manually input values in and then have a button to either save the data to a new record or row in my spreadsheet or delete it from the spreadsheet.

how do I do this? I'm completely lost.

I'm a novice Office user, and Novice code-writer at that so any help would be apppreciated.

Thanks!

[Siddharth Rout]

Welcome to the forums

I'm a novice Office user, and Novice code-writer at that so any help would be apppreciated.

How much time do you have to learn it?

[Silverbreez]

I have time to learn, and I am trying to read what I can, and remember what I've learned in the past but I admit i'm extremely rusty.. if not a total novice again. I'm familiar with Excel, but not the VBA side of it

[Siddharth Rout]

I have time to learn, and I am trying to read what I can, and remember what I've learned in the past but I admit i'm extremely rusty.. if not a total novice again. I'm familiar with Excel, but not the VBA side of it

In that case We can help you

Do you want to manually type the value in the textbox or scan it into the textbox from a barcode scanner?

[Silverbreez]

My Plan was to have the value be scanned in, the barcodes i have are a simple alphanumeric string.. first two letters are location, next letter is for monitors only, then a 3 number designation.

For example.. I have a workstation SB010 and it's monitor is SBM010 for the monitor designation.

I just want to have it where I have a running list of values say 010 to 500.. static (I'm a small company) and when I scan the barcode right now it auto-inputs the text from it, but in this instance it would be into a text box. then it would search for and display (if there was any..) data into the text boxes for the other 3-4 pieces of data i'm tracking. otherwise it would be blank, and I would manually input values and click a button to save it into my spreadsheet or another button to delete it from the spreadsheet.

also, the total monetary value that I have calculated in a formula i would like to keep displayed on the form as a label so that when entering a new machine it would update my total value of my assets.

Hopefully i'm not getting to complex, but that's the entirety of what I'm attempting to do. sof far i have a spreadsheet with all my current data that I would like to use as a data source if possible.

[Siddharth Rout]

Ok to begin with, which bar code reader are you using? Do you have an SDK kit with the bar code reader?

[Silverbreez]

Unfortunately I do not have an SDK for it

[Siddharth Rout]

Check the manufacturer's site for details. Without it we will not be able to scan values in vba... If I am not wrong...

Are you okay to type values manually for the time being...

[Silverbreez]

currently, I have it working with my personal workstation, and it scans in my barcodes, and whatever program I have open that accepts input it inputs the correct data from the barcode. but if I have to for the moment yes, i'm ok with manually entering data

[Siddharth Rout]

Okay

Your first lesson

http://www.contextures.com/xlUserForm01.html

Confirm that you understand this once you are done, we will take it from there...

[Silverbreez]

Confirm that you understand this once you are done, we will take it from there...

I understand

I went through the lesson twice, and added the form from the lesson (With my own tweaks from my data and setup) to my spreadsheet and tested it's functionality. It works perfectly so far to add data where I want to add it and how I want it added.

Next i need to know how to do the reverse, and delete a line from the database, or edit an existing one. great information thanks so much for the help thus far!

[Siddharth Rout]

Lesson 2

Understand how .Find works

Search this forum and you will find plenty of examples...

[RobDog888]

A barcode scanner works just like a keyboard. Set the input focus to your textbox, scan your barcode and the numbers will be automatically entered.

Check your manual to see if a carriage return is automatically appended or if its a setting.

[Silverbreez]

Thanks RobDog, the scanner works great at the mement, no carriage return, and for the moment I have no program to change that setting.

Also, been searching and looking at the .find command, but not sure I fully understand what it's doing or how it's doing it. at least from my own perspective.

forgive my "N00bness"

[Siddharth Rout]

.Find is the fastest way to search for something in an Excel sheet.

This will be used to find a text in your workbook and when found, we can either delete the row which has that text or edit as per your requirements...

Let me know if you want an example for the above?

[Silverbreez]

'~~> Search srchstring if it already exists
Set aCell = ws.Cells.Find(What:=srchctring, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
'~~> If Found
'~~>Your code here for copy and paste special
Else
'~~> If not found
MsgBox "Not Found"
End If

I've been looking at this code, but the find srchstring and the rest of the arguments don't make sense to me

do i have to hard-code each thing i would ever want to search for? or how can i use the value input into a textbox, like the ones I just learned in the above posts, to be input into the find string.

[Siddharth Rout]

Here is an example...

Hope it helps...

[Silverbreez]

I kind of understand, but the code in the macro doesn't quite click for me. the search & delete button is exactly what I want, but is there a way code-wise to instead of hard-code a value to search for, .. instead popup a simpe message box with a text box to imput what you want to search for and delete?

[Siddharth Rout]

Yes search the forum on inputbox()

[Silverbreez]

Perfect! I made the inputbox exactly where I wanted it,

[Siddharth Rout]

Gr8! If your query is resolved then do remember to mark the thread resolved

Check the link in my signature on how to do it...

[Silverbreez]

Hmm, i made the input box and it works the way I like, however i discovered an unwanted function.. when the input box comes up it has either an "ok" button or "Cancel" button, when you click cancel, it deletes the first row in the spreadsheet. how do I nullify that from happening?


Here's my Code:

Private Sub cmdDelete_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Workstations")

Dim aCell As Range, intRow As Long
    Dim srchstring As String
        
    '~~> Looking for the string
    srchstring = InputBox("Enter ID you wish to Remove")
    
    '~~> Search srchstring
    Set aCell = ws.Cells.Find(What:=srchstring, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        '~~> If Found
        intRow = aCell.Row
        Rows(intRow).Delete
    Else
        '~~> If not found
        MsgBox "Not Found"
    End If
End Sub

[Siddharth Rout]

Private Sub cmdDelete_Click()
    Dim iRow As Long, ws As Worksheet
    Set ws = Worksheets("Workstations")

    Dim aCell As Range, intRow As Long
    Dim srchstring As String
        
    '~~> Looking for the string
    srchstring = InputBox("Enter ID you wish to Remove")
    
    If Len(Trim(srchstring)) = 0 Then
        MsgBox "There is nothing to search"
        Exit Sub
    End If
    
    '~~> Search srchstring
    Set aCell = ws.Cells.Find(What:=srchstring, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        '~~> If Found
        intRow = aCell.Row
        Rows(intRow).Delete
    Else
        '~~> If not found
        MsgBox "Not Found"
    End If
End Sub

[Silverbreez]

perfect Koolsid I must say.. Thank you for all your help, and everyone else too!! this is working better than I had hoped! still putting finishing touches on it though...

[Siddharth Rout]

perfect Koolsid I must say.. Thank you for all your help, and everyone else too!! this is working better than I had hoped! still putting finishing touches on it though...

Gr8!

Do remember to come back and mark your thread resolved if your query is solved. Check the link in my signature on how to mark the thread resolved

[Silverbreez]

ok,

so far with help (read: Koolsid...) I've created my working spreadsheet complete with forms and working buttons on each that do what I want.

I would also like to have a button to search the spreadsheet for a certain value, and when found display the row's information in appropriate text boxes on the associated form. (ie.. i want to search by Asset ID.. and then be able to pull up individual asset's data into the form, from the spreadsheet.

how do I map the values in reverse back into a textbox?

[Siddharth Rout]

I guess this kind of queries have already been covered in the forum... search them. If you get stuck, simply post and we will definitely help