Inventory Spreadsheet (im a newb with VBA)

[Tril]

Hello everyone,

We are a small company and i would like to add some QOL things to our excel spreadsheet that we use for inventory in excel 2019

Column A: Job number
column B: Part number
Column C: Quantity
column: D Client

I know how to add a macro to a button and stuff like that but i aint that good in VBA, i know some very basic stuff and this is out of my league. Ive looked on google,found some macros here and there but for some reason the macros dont work,maybe im doing something wrong or my excel version isnt compatible,idk.

I would like to have a floating form always on screen in excel. I would like the Form to take the information from whatever i select, for exemple, if i highlight Row 26, then the column A26 will always go to the Job number field on the floating form, and so on for column B-C-D

on top for that i would like a print button on the floating from that print the information of the selected row

In Resume: I want to click a Row, the information from the different columns are copied to a floating form and then i print the form.

can you guys help me out?

thanks in advance.

 

[DacEasy]

Dim sWorkbookName As String
Private Sub btnConnect_Click()
    Dim iCnt As Integer
    For iCnt = 0 To ListBox1.ListCount - 1 'Reads the items in the ListBox
        If ListBox1.Selected(iCnt) = True Then  'Check ListBox to see if the item is selcted or not
            sWorkbookName = ListBox1.List(ListBox1.ListIndex)
            Workbooks(sWorkbookName).Activate
            txtFile.Value = sWorkbookName
        End If
    Next
End Sub
Private Sub btnFill_Click()
    If sWorkbookName = "" Then 'Make sure a WorkBook has been selected
        Exit Sub
    Else
        Workbooks(sWorkbookName).Activate 'Verify the right WorkBook is selected
        UserForm1.txtJobNumber.Value = Cells(ActiveCell.Row, "A").Value
        UserForm1.txtPartNumber.Text = Cells(ActiveCell.Row, "B").Value
        UserForm1.txtQuantity.Value = Cells(ActiveCell.Row, "C").Value
        UserForm1.txtClient.Text = Cells(ActiveCell.Row, "D").Value
        UserForm1.txtCurrentRow.Text = ActiveCell.Row
    End If
End Sub
Private Sub btnPrint_Click()
    If sWorkbookName = "" Then 'Make sure a WorkBook has been selected
        Exit Sub
    Else
        Workbooks(sWorkbookName).Activate 'Verify the right WorkBook is selected
        With ActiveSheet.PageSetup
            .Zoom = False
            .FitToPagesTall = 2
            .FitToPagesWide = 1
            .Orientation = xlLandscape
        End With
        Range("A" & ActiveCell.Row & ":D" & ActiveCell.Row).PrintOut Preview:=True
    End If
End Sub
Private Sub btnClear_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls 'Clear all TextBoxes
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""
        End If
    Next
    Me.ListBox1.Clear
End Sub
Private Sub btnExit_Click()
    Unload Me
End Sub
Private Sub btnList_Click()
    Dim objXL1, WB1
    Set objXL1 = GetObject(, "Excel.Application")
    Set WB1 = objXL1.ActiveWorkbook
    Me.ListBox1.Clear
    For Each WB1 In Application.Workbooks 'Fills in the ListBox with all open spreadsheets
        Me.ListBox1.AddItem WB1.Name
    Next
End Sub

I tried solving your issue. I created a UserForm and added the code above to it. In order to get the form to free float you will need the code below in the module. I hope this helps.

Public Sub show_form()
UserForm1.Show vbModeless 'Allows the UserForm to free float
End Sub

 

[DacEasy]

Click Update List to see a listing of open spreadsheets
Once you select the one you wish to use, click Connect
Select your row, and click Fill, from there you can Print the information.

 

[Tril]

Thank you DacEasy, it wasnt easy but i think i got your code to work

 

[DacEasy]

Good Job, glad you were able to accomplish your goal.