VBA Help with Inventory manager (without a userform)

[BRUNO21]

Afternoon All, Anyone bored and want to help me!(Stuck for days)

I have pics of the project I'm working on.
I am trying to make a macro that would take(when clicked) the contents of "ADD PICTURE", (the text box below it) and enter it into the correct "Picture column"
I have linked my code, and my first macro worked perfect. (the text boxes in Col J and P)- but having no luck trying the same with the add picture. appreciate any and all help.
Not sure what to do bc i can't just say, put contents in the last row of col "o" due to; not every test mate having a pic.
If there's a better way of writing the code, I'm all eyes/ears. hopefully this makes sense. essentially all i need is to have it where someone would enter data into the blank white cells, and it would save to the corresponding place below.
ps. some fields might be blank and i was thinking of putting a - , as a place holder so program doesn't insert data wrong. but could not figure it out
I need it to keep all data in the table together, across columns I-Q(anyone know of a way i could sort by TC number greatest to least, or vice versa, *but with out mixing up what goes together. ie; HF-58 needs to stay with all data to the right; MFG part number etc,manufacturer,pin/socket...etc)
Not sure what to do bc i can't just say put contents in the last row of col "o" due to not every one having a picture.

 

[severynm]

Hello!

If your users type something into the Add Picture cell, how do they (or the spreadsheet for that matter) know which row of the table to add that content to? I think you need to ask for another piece of information here. Determine which of your columns will contain a unique value (no duplicates) - whatever column you determine to be the "reference" you can then find the row in the main table that matches a specified value using MATCH. =Match(UniqueLookupValue,ReferenceColumnToLookupValueIn,0) will return the row of your table that matches the value you want to look up.

Regarding sorting/filtering your data, look at formatting your main table as a Table (capital T). Those functionalities, plus many more, are built in.

 

[BRUNO21]

Hello!

If your users type something into the Add Picture cell, how do they (or the spreadsheet for that matter) know which row of the table to add that content to? I think you need to ask for another piece of information here. Determine which of your columns will contain a unique value (no duplicates) - whatever column you determine to be the "reference" you can then find the row in the main table that matches a specified value using MATCH. =Match(UniqueLookupValue,ReferenceColumnToLookupValueIn,0) will return the row of your table that matches the value you want to look up.

Regarding sorting/filtering your data, look at formatting your main table as a Table (capital T). Those functionalities, plus many more, are built in.

Thanks for the reply,

how could i implement your reference idea with the "TC/HF Mate Number" col? in this col, there would be some duplicates "(for instance, if they build more than 1 TC-12 there would be more than one entry in that col ) but that would be ok, as i would like the pic file location to be entered into each one.
also, i just turned the data into a table, so ty for the tip.
i'm not really understanding your example.
another workaround i was thinking, is to add a "ToLastRow bttn" that would take user directly to last entered data, then they could just insert the pic via link right there. any ideas on that?

 

[severynm]

Oh ok - finding multiple matches is a little more complex but not too bad overall. You essentially need to loop over all of the items in the TC/HF column, and add the text in the appropriate Picture row for each match. How familiar are you with basic looping? I'll provide the following code with explanation, which would then be incorporated into the Add Picture button:

Sub AddPicture()
    Dim idx As Long 'we need a variable to keep track of which row we are in the table, aka an index
    Dim TCHFMateNumberToMatch As String 'this is the string we are looking for in the first column of the table
    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName") 'to help simplify the code we assign the table to an object
    TCHFMateNumberToMatch = SheetName.Range("D2").Value 'set the number to match based on the correct location of user input
    
    For idx = 1 To mainTable.DataBodyRange.Rows.Count 'looping over each row in the table, from 1 to the total number of rows (.Count)
        If mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(idx, 1).Value = TCHFMateNumberToMatch Then 'for each idx, check if the value in the TC/HF column matches what we are looking for
            mainTable.ListColumns("Picture").DataBodyRange(idx, 1).Value = "Match" 'if it does, add the correct data in the Picture column
        End If
    Next idx
End Sub

You will need to set SheetName and TableName for whatever you have named your worksheet and table, respectively.

---

Regarding the button to add the picture link to the last row - I dont know your use case; only you can answer if that is something that would be beneficial. This will select the cell in the TC/HF column of the last row of your table:

    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName")
    
    mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(mainTable.DataBodyRange.Rows.Count, 1).Select

Let me know if you still have questions....

 

[BRUNO21]

Oh ok - finding multiple matches is a little more complex but not too bad overall. You essentially need to loop over all of the items in the TC/HF column, and add the text in the appropriate Picture row for each match. How familiar are you with basic looping? I'll provide the following code with explanation, which would then be incorporated into the Add Picture button:

Sub AddPicture()
    Dim idx As Long 'we need a variable to keep track of which row we are in the table, aka an index
    Dim TCHFMateNumberToMatch As String 'this is the string we are looking for in the first column of the table
    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName") 'to help simplify the code we assign the table to an object
    TCHFMateNumberToMatch = SheetName.Range("D2").Value 'set the number to match based on the correct location of user input
   
    For idx = 1 To mainTable.DataBodyRange.Rows.Count 'looping over each row in the table, from 1 to the total number of rows (.Count)
        If mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(idx, 1).Value = TCHFMateNumberToMatch Then 'for each idx, check if the value in the TC/HF column matches what we are looking for
            mainTable.ListColumns("Picture").DataBodyRange(idx, 1).Value = "Match" 'if it does, add the correct data in the Picture column
        End If
    Next idx
End Sub

You will need to set SheetName and TableName for whatever you have named your worksheet and table, respectively.

---

Regarding the button to add the picture link to the last row - I dont know your use case; only you can answer if that is something that would be beneficial. This will select the cell in the TC/HF column of the last row of your table:

    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName")
   
    mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(mainTable.DataBodyRange.Rows.Count, 1).Select

Let me know if you still have questions....

For table name, do i just highlight all of the data in the table and then name it? then put that name into the code you provided?

 

[severynm]

To change the table name, click in the table, go to the Table Design ribbon tab, and name it whatever you want on the far left side in the box called "Table Name". You're correct in whatever you name it would also have to go in the code.

 

[BRUNO21]

Oh ok - finding multiple matches is a little more complex but not too bad overall. You essentially need to loop over all of the items in the TC/HF column, and add the text in the appropriate Picture row for each match. How familiar are you with basic looping? I'll provide the following code with explanation, which would then be incorporated into the Add Picture button:

Sub AddPicture()
    Dim idx As Long 'we need a variable to keep track of which row we are in the table, aka an index
    Dim TCHFMateNumberToMatch As String 'this is the string we are looking for in the first column of the table
    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName") 'to help simplify the code we assign the table to an object
    TCHFMateNumberToMatch = SheetName.Range("D2").Value 'set the number to match based on the correct location of user input
   
    For idx = 1 To mainTable.DataBodyRange.Rows.Count 'looping over each row in the table, from 1 to the total number of rows (.Count)
        If mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(idx, 1).Value = TCHFMateNumberToMatch Then 'for each idx, check if the value in the TC/HF column matches what we are looking for
            mainTable.ListColumns("Picture").DataBodyRange(idx, 1).Value = "Match" 'if it does, add the correct data in the Picture column
        End If
    Next idx
End Sub

You will need to set SheetName and TableName for whatever you have named your worksheet and table, respectively.

---

Regarding the button to add the picture link to the last row - I dont know your use case; only you can answer if that is something that would be beneficial. This will select the cell in the TC/HF column of the last row of your table:

    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName")
   
    mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(mainTable.DataBodyRange.Rows.Count, 1).Select

Let me know if you still have questions....

so would the code you give me take "image.png" and enter it into the last cell in pic col?
if not, how should i go about telling excel where it goes. if i'm understanding correctly, your code takes the input of d26 and puts it where?
ps. i have been manually adding the pictures you see listed already

 

[BRUNO21]

Oh ok - finding multiple matches is a little more complex but not too bad overall. You essentially need to loop over all of the items in the TC/HF column, and add the text in the appropriate Picture row for each match. How familiar are you with basic looping? I'll provide the following code with explanation, which would then be incorporated into the Add Picture button:

Sub AddPicture()
    Dim idx As Long 'we need a variable to keep track of which row we are in the table, aka an index
    Dim TCHFMateNumberToMatch As String 'this is the string we are looking for in the first column of the table
    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName") 'to help simplify the code we assign the table to an object
    TCHFMateNumberToMatch = SheetName.Range("D2").Value 'set the number to match based on the correct location of user input
   
    For idx = 1 To mainTable.DataBodyRange.Rows.Count 'looping over each row in the table, from 1 to the total number of rows (.Count)
        If mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(idx, 1).Value = TCHFMateNumberToMatch Then 'for each idx, check if the value in the TC/HF column matches what we are looking for
            mainTable.ListColumns("Picture").DataBodyRange(idx, 1).Value = "Match" 'if it does, add the correct data in the Picture column
        End If
    Next idx
End Sub

You will need to set SheetName and TableName for whatever you have named your worksheet and table, respectively.

---

Regarding the button to add the picture link to the last row - I dont know your use case; only you can answer if that is something that would be beneficial. This will select the cell in the TC/HF column of the last row of your table:

    Dim mainTable As ListObject
    Set mainTable = SheetName.ListObjects("TableName")
   
    mainTable.ListColumns("TC/HF Mate Number").DataBodyRange(mainTable.DataBodyRange.Rows.Count, 1).Select

Let me know if you still have questions....

where would i tell excel which TC/HF number goes with the pic? should i add another textbox that a user would enter ...TC-123 and the picture would be tied with that in the pic col?

 

[BRUNO21]

sorry for all the questions; just one more. is there any way i can get excel to load the image(those links under pic col)in the box i have at top labeled "Picture"?

 

[severynm]

so would the code you give me take "image.png" and enter it into the last cell in pic col?
if not, how should i go about telling excel where it goes. if i'm understanding correctly, your code takes the input of d26 and puts it where?
ps. i have been manually adding the pictures you see listed already

Kind of. Looks at the entire table by going down each row in the TC/HF column and looking for matches to the desired TC/HF number. If a match is found, it will add "image.png" into the Picture column of that row. Do you just want "image.png" to be added to the last row? Or to any row in the table? Im not totally sure what you're looking for.

where would i tell excel which TC/HF number goes with the pic? should i add another textbox that a user would enter ...TC-123 and the picture would be tied with that in the pic col?

Yes exactly - you would need to add another input...that's what my question was aiming towards.

sorry for all the questions; just one more. is there any way i can get excel to load the image(those links under pic col)in the box i have at top labeled "Picture"?

So do you want the images to actually embedded in the worksheet? I don't know anything about the size of the photos you are looking to insert, but unless your photos are a few kb in size, I would caution against that, as your spreadsheet size will quickly become massive, even with a few hundred rows of data in the table. That means there's a few hundred photos in the spreadsheet. The links will open in your default photo viewer, but I think there's also some ways you could make a popup of some sort either through comments or a userform. I don't have recommendations for you as I've never done that sort of thing before. If this is a necessary feature, to be honest I think excel is not the best tool for the job.