Start Free Trial

asked on

Items Locator Excel Workbook

What I was looking for here was assigning items(things) to headings and then add photos for every item assigned.
Areas covered for now are Bedroom, Lounge, HotWater Cupboard, Shed and Insurance.

Would also need an option to add more areas manually if needed.

So how this would work is I click on Clothes Cupboard for example and drop down menus would be Shelf 1, 2, 3, 4. I would have the option to input data in Shelf 1, once I inputted the data I also needed to display the photograph of the item and the photos would be stored in local storage on my computer.

When i click or hover on the text item , the photo should automatically display on hover or clicking of the item. I have attached a excel file for an idea, but if it is not clear, then let me know then I will draw it on paper photo it and then upload it to be used in Excel.

File Attached : Item Locator.xlsx sample file

I think that the design needs to be changed a bit. For example in the Bedroom section you have Clothes Cupboard and Bed Drawers in the same row but in that row there's only one photo cell and you would need two. So maybe the sections should look like this.

User generated image

ASKER

Yes that should be fine .

If Shelf one had 20 photos, I would need to type 20 text to describe them.

I could make it so that if you clicked on Shelf 1 for example it would ask you "Insert new photo?" and if you answer "Yes" it would ask you to select the image you want to insert. It would then add a line between the Shelf 1 and Shelf 2 rows and the image would be inserted in the new line.

What is the file extension for the photos you will be using? JPEG? BMP? TIFF?
If you are planning on storing the photos in a single folder please let me know the name of that folder?

ASKER

the folder name would be called "Items 22"

Mostly the photos would be png or jpeg. I will try not to have bmp or tiff. but what if there are some few old photos that I might want to insert , then would I need a photo converter to change it to png images

I could make it so that if you clicked on Shelf 1 for example it would ask you "Insert new photo?" and if you answer "Yes" it would ask you to select the image you want to insert. It would then add a line between the Shelf 1 and Shelf 2 rows and the image would be inserted in the new line.

Yes that would be nice.

The main part of this table is to add a SEARCH button, to search any item in the table which would then display the Category area and in which shelf, drawer, etc the item is located in.

What exactly would it search for?

ASKER

What exactly would it search for?

the item name.

example if I am search for a charging cable for USB Radio

I would enter Radio in the Search to bring up the details

Another example is "Smartphone Tool Kit"

So if I type : Toolkit, then would show up it associated data , i.e. the location of the item whether it is in the living room, bedroom,etc and on which Shelf it is situated on 1, 2 or 3 .

But I have Toolkit for Computer as well, so now if I type Tool Kit it should show up two results for toolkit i.e. Smartphone Tool Kit, Computer tool Kit and so on.

So I guess we need a column to the right(?) of the photo or do you have a different idea?

ASKER

So I guess we need a column to the right(?) of the photo or do you have a different idea?

I did not get what you meant ?

Where would the search find the name of the item?

ASKER

Where would the search find the name of the item?

The search would source it from the item names typed in the text column and yes that would be a column to the right of the photo as per your previous comment

the folder name would be called "Items 22"

What would the full path be?

ASKER

What would the full path be?

Items 22 would be listed under Documents Folder.

Path : C:\Users\eperei\Documents\Items 22

is it possible to allow an option to edit the path after Users as sometimes I have eperei, sometimes I have "kwik" for another laptop.

ASKER

I think I will place the photos on an external hard disk.

since I am using different computers.

ASKER

if I use an external drive the default path is D:

ASKER

If you would allow path update popup to edit the path, for that I would need a update path button, like you did for the keno update file would help, just incase I change the path.

Here is a very preliminary version. In column 'A' click Shelf 1, 2 or 3. When you do, a Browse dialog should open set to the file path you specified. At the bottom you should see something like this.

User generated image

The right side is set up so that it will only show you jpg, jpeg or png pictures. If you want to look for any other type of picture click the downarrow at the far right and select All Files. Note that I can change what types of files are Pictures to include TIFF or any other type you want.
29248453.xlsm

ASKER

I did a test by inserting a photo but this what I get.

I inserted the blue T shirt photo and then the description in below picture.

Next I inserted a Brown T shirt photo, but it overlapped the blue T shirt in below picture.

so how do I type the text for Brown T shirt, when blue is already typed in above picture? and how to avoid the overlapping? and make each photo have its own cell to type the text ?

Hmm, the code is supposed to set the dimensions of the picture. What type of picture are those?

ASKER

They are png files, the smallest file size.

Blue t Shirt was 132 kb and brown shirt was 416 kb

ASKER

So when I insert the picture it does allow me to resize , which I did, but when I insert the next picture , this next picture does not have its own dedicated cell ?

or are all pictures meant to stay in one cell, but then I won't be able to type text next to each photo for search purposes

If you don't mind doing so, please attach both pictures.

So when I insert the picture it does allow me to resize , which I did, but when I insert the next picture , this next picture does not have its own dedicated cell ?

Do you mean by that you attempted to add both pictures to the same cell?

I've also thought about the fact that you could potentially want to have hundreds of photos which would make the workbook very large. Handling photos via VBA is new to me so I'm hoping that code to compress/resize photos exists already.

ASKER

Do you mean by that you attempted to add both pictures to the same cell?

There was no option to move to next cell

ASKER

potentially want to have hundreds of photos

Each Shelf will have roughly 20 to say max upto 50 clothes.

But on an average it is about 25 clothes per shelf.

However for electronic parts it could touch just above 50 photos .

ASKER

Blue T Shirt.PNG

Brown T shirt.PNG

ASKER

Is it possible to make the picture look big , only when hovering the mouse pointer over the picture.?

If that is possible, then even if the picture is small in the cell, then on hovering over photo shows it as a decent size will solve the issue of me resizing the photo for every picture.

Is it possible to make the picture look big , only when hovering the mouse pointer over the picture.?

Sorry, no, that's not possible. I don't have one, but if your mouse has a mouse wheel you can zoom the page by pressing and holding Ctrl and moving the wheel forward or back.

One thing I could do is to make the pictures bigger to begin with.

Give this a try. I've added tabs for each location and so far only the Bedroom tab is functional.

As you know to add a new picture you click in a cell in one of the locations like 'Shelf 3' or 'Drawer 2'. However there may be occasions when you might want to change locations like, say 'Shelf 3 etc' to 'Shelf 3' or add a new shelf etc., and on those occasions you don't want it to locate a picture. To get around that I added a toggle button which says 'Turn Maintenance On'. When you click that button it changes to 'Turn Maintenance Off' and you are free to make any changes you want in the location. Click the button again when you are done making changes.

You will also see a box (a textbox) next to 'Search' in the first row. How it works is this: Let's say you have a description that says 'My favorite shirt'. If you then type in any part of that description like 'vor', all the description cells that contain that value will turn yellow. To get rid of the yellow, clear the box.
29248453a.xlsm

ASKER

What is the sequence of the steps ?

ASKER

If I understand correctly, before I start I already see cell C4 highlighted in yellow.

So what does that color represent in the process?

Is my steps as follows ?

1) Turn Maintenance ON is the setting

1) Click on Shelf 1 to insert a picture

2) The picture gets inserted in the cell B4 , but then the cell below gets yellowed too in below picture

so not sure why ?

3) I entered the text Blue T shirt , but when I pressed enter, it opened a dialogue box,

So what am I doing in this with the dialog box popped up. ?

I already entered the blue t shirt in cell b4, so I am confused why it is looking for pictures again when I press enter in cell C4 after entering the text.

ASKER

since the picture is small in the photo column , how do I view it in normal size ?

I thought the shortest way to view the photo in normal size would have been to hover over the text to pop up the photo or hover over the photo to pop up the photo, but you are saying that might not be possible.

1) I should have cleared the Search before I uploaded the workbook. Do that and it will solve the yellow color problem.
3)

I entered the text Blue T shirt

I can't be sure but I'm guessing that you entered the description in column 'A'; the description should be added in the appropriate 'Description' column.

I fully tested this before I added the search box and it was working fine, but after that I just tested the Search process which worked fine, but it seems that adding the textbox severely messed up adding the pictures. I'm working on fixing that now.

This should work better. To add the first picture click a cell in column A or D. Assuming you chose column A, then to add a second picture click the in the new row that appears under the first picture (or any other cell in column A or D).
29248453b.xlsm

ASKER

Now after typing the description in cell D4 and pressing enter key, a dialogue box pops up.

Not sure what steps to follow here when the dialog box opens in below picture ?

Descriptions should only go in columns C and F as the headings describes. When you click in D4 it means you want to add a picture in E4, just like clicking a cell in column C adds a picture to column B.

Do you have problems with the font size of the headings?

ASKER

Dialog box popping up even though text is typed in C4

By mistake I said cell D4,

I was referring to Cell C4. So I typed Blue T Shirt in cell C4 and pressed enter key,

the cursor automatically moved to cell D4 (which is Drawer 1) and then immediately a dialog box opened in below picture.

Now I am beginning to understand what is happening.

That means a blank column has to be inserted between C4 and D4 to resolve the issue or a return key has to be coded in.

Do you have problems with the font size of the headings?

Could you please make the headings a decent size, including the shelf and drawer fonts.

Attached is an updated workbook that uses that uses a larger font in the Bedroom tab.

After typing the description you don't have to press Return.

Apparently however, the default action for the Return key in your Excel is to go to the right. To change that and have it go down instead as it does in my Excel:

Select 'File' in Excel's top row
Click 'Options' and then 'Advanced'
Change the Direction setting to 'Down' as shown in the picture
Quit Excel

Note that this will effect all of your workbooks.

User generated image

ASKER

Thanks for the larger fonts.

Just wondering if I want to take a direct photo on the Android tablet as an alternative to inserting image from the folder.

Is the direct photo clicking option also there at the same time.?

I know nothing about Android but I found this article.

The tabs in the attached workbook are all functional.
29248453d.xlsm

ASKER

Ok thanks I will check it out

ASKER

I was just wondering about the size of the photo in excel.

The photo sizes in the B column , are they smaller size, than if I would have opened the those photos directly in Items 22 folder ?

Please show me a picture of the problem. If this involves the direct Android photo transfer I may not be able to help you.

If a picture is too small, select it and the drag one of the corners until it is big enough. Dragging a side will change the aspect ratio.

ASKER

Please show me a picture of the problem.

No it is not a problem.

What I meant was, whatever photo size is in items 22 folder, does it show the same size in the B photos column in the workbook ?

All the photos in the workbook will have the same height. The width will vary so as to maintain the aspect ratio.

ASKER

ok. what about if I want to take a photo directly from my windows 10, 11" laptop,

is there a possibility to add the second option of "Take a picture", just like how the Android tablet has an option to "take a picture"

Sorry but I don't but I don't have any idea, so please close this question and ask a new one about that and maybe someone else will know.

ASKER

This Version of Microsoft Excel can't run VBA macros

Before I close the question, I am getting this message on the android tablet cannot run this version of Excel.

I am running Excel 365 .

Could you kindly make your excel version compatible with Excel 365 ? so I can use it on the Android tablet.

Have you used the Android tablet with Excel 365 workbooks before?

In any case I did some research and what I found is (and don't let me be the last word on this) that you can't use VBA on an Android device.

ASKER

Have you used the Android tablet with Excel 365 workbooks before?

Yes.

and don't let me be the last word on this) that you can't use VBA on an Android device.

In that case is it ok to ask the community ?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Search function not working in the workbook

workbook attached. All I did was modified shelf 1 to Left Top shelf and closed maintenance

29248453d photo input.xlsm

You don't get this?

User generated image

ASKER

No it doesn't

ASKER

I tried opening and closing workbook 3 times

Totally quit Excel the reopen the workbook.
If that doesn't work, quit Excel and reboot.
If that doesn't work let me know.

ASKER

Forgot to mention that m using the workbook on office 365 laptop. That is what I will also use

Has that affected search function?

Has that affected search function?

Has it ever worked before when you've used that workbook?

ASKER

First time I have tried. And will use it as default device for future use.

But I never retried updated workbook on the desktop pc

I can't help you with Office 365 problems or solutions. Let me know what happens when you do try it on your desktop PC.

Please see This Microsoft article. In that article it says:

"Excel macro-enabled workbook (.xlsm)
A workbook in this format can be opened but macros do not run in a browser window." The bolding is mine.

That means that to search for anything you'll need to use Excel's Find and Select function since my Search uses a macro.

ASKER

Let me know what happens when you do try it on your desktop PC.

one of the shirts was titled "squares" and on the desktop pc when I typed squares in the search box it highlighted the yellow colour, but the yellow selection was not within visible range.

The only way I had to do check out was to scroll down manually.

But when I typed "C" it highlighted all the "C" , but when I completed typing Chisel, the highlight was missing on Chisel. So not sure why the search function is not behaving consistently

But suppose if I have 50 items in the Items 22 folder, which will happen in the next few days as catalogueing takes time and if I search for the 49th item (torch) then the 49th item would not be in the visible range of the workbook and that would require recoding to bring it to visible range.of the opened workbook.

Please attach your workbook.

ASKER

Workbook attached

29248453d photo input.xlsm

I made some changes in the Bedroom tab including making the search case-insensitive. Try it out and let me know if it is satisfactory.
29248453e.xlsm

ASKER

Yes the search is working now. thanks

Now the issue is that the selection is not autoscrolling in visible view range

I have to manually scroll to look for the selection after typing in search.

I should have said to repeatedly click the Find Next button. Each time you click it the next yellow cell will be brought to the top.

ASKER

Oh yes, I did not realise that.

Was wondering , what if I have the same item(Item 1) in the Bedroom and second similar item in the Lounge . Is it possible to have a source finder or path where the item is found ?

Otherwise I have to keep typing in description "Item 1 in Bedroom" and if the similar item is in the lounge due to lack of space in the bedroom, then I would have to type "Item 1 in Lounge"

If a path could be added to the found item, only if they are located under different tabs.

Found 2 : (Bedroom / usb cable) , Lounge /usb cable)

I add a usb cable to Bedroom and another usb cable to the Lounge tab.

But on searching it could find the item only on one tab bedroom , not across tabs

If you like the way Search works in the Bedroom tab then let me know and I will do the same for the other tabs.

As for across-tab searches I can probably do something, but please ask a new question.

I went ahead and changed the other tabs. I also temporarily hid the Maintenance buttons because I made some other changes which I believe do away with their need. Let me know if you come across a situation where you need one.

29248453f.xlsm

ASKER

hid the Maintenance buttons

I would need the Maintenance tab from time to time.

So I did not understand when you said you hid it temporarily ?

If you like the way Search works in the Bedroom tab then let me know and I will do the same for the other tabs.

ok will do.

So I did not understand when you said you hid it temporarily ?

Excel provides an easy way to hide and unhide buttons and so in this case I hid the Maintenance buttons to see if in the latest workbook you can now do without them. If you can't then please describe the situation(s) in the latest workbook where you need it and I will either change the code to eliminate the need or I will unhide the buttons.

Nevermind, I found a need, so if you are otherwise happy with the latest workbook I will unhide them.