We help IT Professionals succeed at work.

excel workbook search forms not working on home pc

i need to add 2 additional features to the search form in the excel workbook but when it opens on my home pc it produces error msgs & will not work.
At work, it works perfectly.
Could you please change the code so it works for me at home also. I have excel 2016 at work & at home.
zSearch-form_shape.xlsm
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A password is being asked for.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If you have the same setup at home as you have at work, I would close my home Excel and reopen and if that doesn't fix the problem then reboot.
Frank .SBuilding Estimator

Author

Commented:
sorry experts, pwd removed & workbook re-attached
zSearch-form_shape.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It looks to me like you are not using the same workbook in both places. A few weeks ago I supplied you with a workbook that will work in 64-bit, but the one you attached here still refers to tvFound which is a treeview control, and treeview controls are not supported in 64-bit.
Frank .SBuilding Estimator

Author

Commented:
i use this workbook at work every day, but today i brought it at home & does not work.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You must have brought home the wrong workbook because the workbook you posted will not run in 64-bit.
Frank .SBuilding Estimator

Author

Commented:
no, i only have the 1 workbook i use at work & its this one, i have rechecked.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here is the correct workbook.
Frank.xlsm
Frank .SBuilding Estimator

Author

Commented:
no, this workbook you have posted contains errors - see below[embed=file
error 1
error 2
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
All I can say is that the workbook that you posted can not work on 64-bit - ever. Compare the sizes of the two workbooks; I'm guessing that the sizes are not the same.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's another way you can verify that. Upload the workbook you attached in this post and try to run it at work. I bet it won't work.
Frank .SBuilding Estimator

Author

Commented:
i use excel 2016 on both work pc & at home, so i just dont get it...it must be something else thats causing this to happen, but thankyou nonetheless.
Frank .SBuilding Estimator

Author

Commented:
ok ill try that when im at work & let you know.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
ok ill try that when im at work & let you know.
Any results?
Frank .SBuilding Estimator

Author

Commented:
hi martin, it works for me at my work place, just at home it does not work
- i just wanted a modification on the form so that when it shows the results, i can click onto 1 of the results & it then brings me to my selected item on  the excel sheet.
- can this be done?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Frank, I can only repeat myself and say that the workbook you attached in this post can not possibly be the one that works for you at work. That's because it refers to an object called tvFound which is a Treeview control which 1) no longer exists in that workbook, and 2) even if it did, Treeview controls are not supported in your version of Excel.
Frank .SBuilding Estimator

Author

Commented:
ok i dont mean to cause you any frustration, my apologies
- i have attached the excel workbook that i use each day at work, could you add this feature described in my previous msg to the search form?
zSearch-form_shape_20.02.01.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The workbook is asking for a password.
Frank .SBuilding Estimator

Author

Commented:
sorry martin, i have removed
zSearch-form_shape_20.02.01.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In this picture that I've artificially colored, which of the items when clicked, would you like to  bring you to your selected item on the excel sheet? The green ones? The Yellow ones? Or both?2020-01-31_14-59-06.png
Frank .SBuilding Estimator

Author

Commented:
how can both be done? i would have thought only 1 could be done..
- also is there a way to open the last search criteria?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I guess I wasn't clear so let me ask in another way.

If you clicked on the item indicted by the yellow arrow would you expect to be taken to cell A43 on the data sheet?
If you clicked on the item indicted by the green arrow would you expect to be taken to cell A43 on the data sheet?
2020-01-31_14-59-06--1-.png
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29170684.xlsm
Frank .SBuilding Estimator

Author

Commented:
hi martin,
- i cant open your attached work sheet because i get a series of errors, compile error & so on....
- yes i would only click on the item idicated by the green arrow and yes i would want to be taken to cell 'a43' of the data sheet.
- also, when i open the search form i would like to be able to open the last search criteria if possible.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The attached workbook is a copy of the workbook you uploaded here. I changed the name to "xyz.xlsm" to avoid confusion. Please open it and let me know what happens.
xyz.xlsm
Frank .SBuilding Estimator

Author

Commented:
hi martin, cant open form, i get the following error msgs...

compile error -1
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Absolutely all I did was to rename the workbook that YOU downloaded. In other words I did not change it. This to me is proof that you are downloading the wrong workbook. Do you agree?
Frank .SBuilding Estimator

Author

Commented:
im confused the workbook i use every day at work is the one i sent you last, it just doesnt work for me on my home pc.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
im confused the workbook i use every day at work is the one i sent you last,
If your environment at work is 64-bit, then that workbook can not work there. If you read the error message you got running my xyz workbook, you'll see that it's telling you that it needs to be updated to 64-bit. Since I didn't change anything in the workbook you downloaded except to rename it xyz.xlsm, that workbook can not be your work workbook.
Frank .SBuilding Estimator

Author

Commented:
hi martin, i have just checked my email that i sent from work to home & this is the worksheet I use at work.
- please retry this one
zSearch-form_shape--1-.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You need to remove the password. Also and I noticed this before, the workbook name ends in .xlsm.doc (as if it's a Word file) rather than just .xlsm
Frank .SBuilding Estimator

Author

Commented:
sorry, pwd removed
zSearch-form_shape--1-.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but that can't be it since it still has this
Option Explicit
Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwnd As Long) As Long

Open in new window

which will cause the same error you got above.

I don't know how your company works but can you verify with someone that you are selecting the correct production workbook? Also, if that's the production workbook, shouldn't it have a more normal name?
Frank .SBuilding Estimator

Author

Commented:
i can fwd you the email i sent myself from my work to my home so you can see..
- it is the correct production workbook, i renamed it because it was originally the one you created for me for my previous work.
- check if this one works for you..
zSearch-Form---Master-1.1.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Again it's calling for a password. It would be easier if you just sent me the password in an EE message. Only you and I can see that message.
Frank .SBuilding Estimator

Author

Commented:
i tried sending you a private msg, did you receive?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Well that workbook no longer has the treeview object and that's good. Since I'm already working on a high-priority problem for someone else and since this workbooks search code is very different from the code in the 29170684.xlsm workbook that I attached above it may take me a few days to update it. Hopefully I can get it done by Thursday.
Frank .SBuilding Estimator

Author

Commented:
ok, so that im clear then this last one i sent you...what does the search form look like & how are the results shown? seems like its very different from the one i use now, as you say this last one has no treeview?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
2020-02-02_16-41-18.png
Frank .SBuilding Estimator

Author

Commented:
that all looks the same, so what is different between the original & the other one?
Frank .SBuilding Estimator

Author

Commented:
the original still does not allow me to open, see screenshot below...
opening error 1
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Which workbook is "the original"?
Frank .SBuilding Estimator

Author

Commented:
as per the one i attached above

"zSearch-Form---Master-1.1.xlsm"
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this version which should open for you.
zSearch-Form---Master-64-bit.xlsm
Frank .SBuilding Estimator

Author

Commented:
hi martin, i am at work now & your wksht does work for me here...i dont know at home though..
Frank .SBuilding Estimator

Author

Commented:
now your xyz.xlms file you posted yesterday does not work for me here at work either, see following error msg.

compile error-1
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The only workbook that you should care about now is zSearch-Form---Master-64-bit.xlsm. Please let me know if zSearch-Form---Master-64-bit.xlsm works in both places.
Frank .SBuilding Estimator

Author

Commented:
i am at home now and your file zSearch-Form---Master-64-bit.xlsm does NOT work, see screenshot below for msgbox shown..

object not available - msg 1
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but this time I attached the wrong workbook and I can't seem to find the right one. In one of our threads I think you said "this works at home". Do you know which workbook that was?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this one.
84bit.xlsm
Frank .SBuilding Estimator

Author

Commented:
this one works for me at work, but not properly when i hit 'clear' in the form it will not clear any of the search criteria i have entered...
im at work now so i cant check to see if this works at home until this evening
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This workbook is the same code as 84bit.xlsm except that
  • It now contains all the sheets
  • The 'Clear' button works
  • Clicking any node in the search results will take you to the row for that result
  • Pressing 'Return' in any of the 3 search form textboxes will perform the search without having to click the 'Search' button.
29170684a.xlsm
Frank .SBuilding Estimator

Author

Commented:
hi martin, im now home and this workbook works, finally!
- once im at work tomorrow i will check it works there too.
- a question, if i add, edit or delete an item in this wksht while at home, then bring to work, will i have any problems?
- so why does this now work and all this time it has not?
- only thing is it possible to add the 'open last search' feature into the form?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
- a question, if i add, edit or delete an item in this wksht while at home, then bring to work, will i have any problems?
If you mean that, say, you delete or change the data in the workbook and then you replace your work workbook with the one you just changed, then no, you won't have any problems.
- so why does this now work and all this time it has not?
Two reasons: 1)  we were working with the wrong workbook much of the time, and 2) I don't have 640bit so I could not test my changes.
- only thing is it possible to add the 'open last search' feature into the form?
Please ask a new question.
Frank .SBuilding Estimator

Author

Commented:
hi martin, just got to work & tested your file above "29170684a.xlsm" and it also works here, thank god!
ill ask a new question for the additional feature, as you requested..
Frank .SBuilding Estimator

Author

Commented:
thankyou martin for your patience & persistance to assist me with this task, FINALLY opening it at both home & work has worked!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome.