excel workbook search forms not working - error msg

Frank .S
Frank .S used Ask the Experts™
on
the attached excel workbook search forms are not working on my home laptop (using windows 10)
- the error msg is "could not load an object because not available on this machine"
- there is 1 only other excel worksheet i need to be added to the attached workbook which i will add once this workbook is fixed
zSearch-Form-Master_19.11.16.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Frank .SBuilding Estimator

Author

Commented:
thankyou martin
Roy CoxGroup Finance Manager

Commented:
To make the code work on both 32 bit and 64 bit systems cahnge the declaration to

Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function LockWindowUpdate Lib "user32" (ByVal hwndLock As LongPtr) As LongPtr
#Else
Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long
#End If

Open in new window


Does your form have a DatePicker on it. This is usually the cause of this error because the DatePicker control is not available on all. computers. I can't tell because the control is not installed on my PC. If this is the case you can install the activeX control. Read this

https://social.msdn.microsoft.com/Forums/en-US/91cf3127-70fe-4726-8a27-31b8964430c5/registering-mscomct2ocx-in-64-bit-windows-7?forum=sbappdev.

here's a CalendarForm available free that will work on 32 bit and 64 bit

Alternate DatePickers for Excel
Frank .SBuilding Estimator

Author

Commented:
Hi Roy, thankyou but can you change the declaration for me please for both 32 & 64bit & send to me.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Roy CoxGroup Finance Manager

Commented:
I've amended that for you.

What is this , tvFound? It causes an error for me.

Also, unrelated to the question but this doesn't make sense using UsedRange

   On Error Resume Next
    .UsedRange.Cells.Offset(1, 0).Interior.Color = xlNone
    'new
'    .Range(.Cells(1, 1), .Cells(1, lngCol)).Interior.Color = 49407
    .Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count)).Interior.Color = 49407

Open in new window

zSearch-Form-Master_19.11.16.xlsm
Frank .SBuilding Estimator

Author

Commented:
thanks Roy, so it can now be used for 32 & 64 bit systems?
- also, i dont understand 'what is tvfound'?
- what doesnt make sense about the 'usedrange' does it cause any problems for the macro to work?
Frank .SBuilding Estimator

Author

Commented:
also, attached is the other sheet i need added to the workbook as the last sheet
zSearch-Form-recipe-sheet-only_19.1.xlsm
Roy CoxGroup Finance Manager

Commented:
That additional code should make the code run on both systems.
UsedRange refers exactly to the whole used range of the worksheet, this can return a false range.

So this is making the row below the UsedRange have no interior colour, which might not be what you want.

I can't see what tvfound is here:


Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    
    For Each ws In Worksheets
        cboForms.AddItem ws.Name
    Next
    tvFound.Style = tvwTreelinesPlusMinusText

End Sub

Open in new window


Did your userform contain a TreView Control, if so then it is missing and may be causing the error message for you.
Frank .SBuilding Estimator

Author

Commented:
yes the userform does contain a treeview control, and ive tested them on each page & they dont work, why??
when i select the "show search" button on each page, it produces an error;
Compile Error
Cant find project or library

I need the search form to work as it always had been, can you fix please.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Frank, I don't know if this will help or not but I worked with you on this project back in March in this thread. and I've attached the most recent workbook from that question. My attempt to convert it to 64-bit didn't work because I don't have and have never used 64-bit. The buttons in the workbook look different than the ones in the workbook you attached, so I don't know what happened there but the frmSearch userform does contain the treeview control that is missing in the workbook you posted.
29138002f.xlsm
Roy CoxGroup Finance Manager

Commented:
If the form had a TreeView Control then I would say it is not available on the new laptop.If you open the form in the wrong version of Excel then that error occurs and the missing control is no longer on the form.

If it is a 64 bit version of Office then the standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.

Jan Karel Pieterse has a non-activeX replacement.

An MSForms (All VBA) Treeview For Access, Excel And Word
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This site offers a Treeview replacement. I have never used it so I don't know if it's compatible with your (my) treeview code.
Frank .SBuilding Estimator

Author

Commented:
thankyou, but the workbook i posted is the one i use and need the treeview control to work, the treeview control code is already in the workbook so can it not be re-activated so it works.
It has always worked when i was at my last work place. Its only now that I need to use it on my home pc that it does not work so id like it to be fixed so i can, instead of having to redo it all again, or does it need to be redone again?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
the treeview control code is already in the workbook so can it not be re-activated so it works.
As I said, I don't have 64-bit, but some research I did confirms Roy's statement that the Treeview control can not be used in 64-bit.

Did you try replacing the treeview that you have with the one in the site that I mentioned in my previous post?
Frank .SBuilding Estimator

Author

Commented:
Unfortunately I don't know how to replace as per your instructions. I don't have the experience or the knowledge to. If you're too busy to help I'll wait & see if another expert may help.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you required to use 64-bit?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This workbook does not use a treeview or any treeview substitute. Instead it highlights the found items in the worksheet. Currently it only works for the 'Data' sheet, and when you open the workbook you'll see the results of searching for "solar".
29164332.xlsm
Frank .SBuilding Estimator

Author

Commented:
i dont believe i need the 64 bit it was Roy that made a comment above on the 32 & 64bit systems, i just want 2 things to work on my posted workbook;
- the column & row hilite on selecting any cell within all the sheets in the workbook, which is working correctly
- the search form with treelist view which is activated once the button "show search form" is selected (which searches all the info on the open sheet & returns a result) Ii have explained this many times & dont understand why it cannot be fixed or wether i need to ask a new question.
If the code on your workbook is the same, can it not be copied & pasted into mine so it works, without having to do all the coding again?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What happens when you search using this workbook?
Frank .SBuilding Estimator

Author

Commented:
i cant use, i get the following error;
data sht_error 1
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this one.
29138002f-a.xlsm
Frank .SBuilding Estimator

Author

Commented:
i get the following msg;
error msg 2compile error
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
it looks like you are running the 64-bit version of Excel.
Frank .SBuilding Estimator

Author

Commented:
ok, so what is involved or what is the quickest fix option?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm not an expert on 64-bit Excel but I believe the best option is this workbook that doesn't use a treeview.
Frank .SBuilding Estimator

Author

Commented:
i understand but i really need the searchform with treeview ..
Frank .SBuilding Estimator

Author

Commented:
has the issue anything to do with the version of windows or office on my pc?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
has the issue anything to do with the version of windows or office on my pc?
If you are uing Excel 64-bit you can't use the treeview.
i understand but i really need the search form with treeview
The workbook I attached that doesn't use the treeview was only a quick and dirty example of what can be done without the treeview and it can be improved. What feature(s) are missing that cause you to say that you need the treeview?
Roy CoxGroup Finance Manager

Commented:
First check which version of Office you are using.

What version of Office am I using?
Frank .SBuilding Estimator

Author

Commented:
the search userform design is in the workbook & looks like;
userform design
im trying to find the vba code for the treelist view in the userform but cant...it should be there somewhere because it has always worked & showed the results in the treelist.

** im using office 2016 **
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Attached is my second try at creating a workbook that does not use a treeview and instead it highlights the values that it finds in the sheet. Like the previous one it is just a quick and dirty example of what can be done without the treeview and it can be improved if you can live with the concept. Like the previous one it currently only works on the 'data' sheet.

Please let me know
  1. If it opens without error
  2. And if it does would doing it this way be okay
  3. What feature(s) are missing?
29164332a.xlsm
Frank .SBuilding Estimator

Author

Commented:
it produces an error once the 'search' button is selected on the 'data' sht, see below.
data sht errorso i cant check if its ok or im anything is missing.
Roy CoxGroup Finance Manager

Commented:
I can look at JKP's solution at the weekend, but I haven't used it before.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This should correct that error.
29164332b.xlsm
Frank .SBuilding Estimator

Author

Commented:
still produces following error;
button error 2
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Go to Visual Basic and then Tools->References. If a reference is "MISSING" then I believe you can uncheck it.
Frank .SBuilding Estimator

Author

Commented:
do i do this in the code or under the developer menu?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In the code.
Frank .SBuilding Estimator

Author

Commented:
i have tried but, wasnt able to change it in the code.
This question has been going back & forth and not really getting anywhere, I have decided to not go ahead any further with this question.
Thankyou for all your help.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this workbook.
29164332c.xlsm
Frank .SBuilding Estimator

Author

Commented:
unfortunately, more errors, cant open.. see attachment screenshot
compile-error_5.PNG
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Delete the line indicated by the green arrow.
2019-12-04_04-45-19.png
Frank .SBuilding Estimator

Author

Commented:
the green arrow is the line above the blue hilite line, which is to be deleted the green arrow line or the blue hilte line?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The green arrow line.
Frank .SBuilding Estimator

Author

Commented:
when i select the 'show search form' button, nothing happens now...
i think there are too many problems for this question now..
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've searched the web and I can't find anyone who says that a userform doesn't work in 64-bit, so please try  this:
  1. Go to Visual Basic
  2. Double-click Module1
  3. Select 'Debug' from the menu bar
  4. Click Compile VBAProject

If Compile VBAProject is greyed out then:
  1. Purposely create an error by deleting the 'S' from 'Sub' in any procedure
  2. Repeat steps 2 and 3 from above
  3. Put the 'S' back
  4. Repeat steps 2 and 3 from above again

Does it tell you that there's an error?
"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Please play the attached video. Are you using the correct workbook which is 29164332c.xlsm?
2019-12-16_07-10-16.mp4

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial