Hi Randy,Thank you for your fantastic video on using the ListView control in VBA! Your explanations are always clear and very helpful.I wanted to share a piece of VBA code that ensures all necessary ActiveX controls, like MSCOMCTL.OCX, are present and correctly registered on the customer's PC. This helps avoid issues related to missing dependencies, especially on different Windows versions (including Windows 10 and 11).I hope this helps your viewers ensure their ListView controls work seamlessly across different systems. Thanks again for all the valuable content you provide! If issues regarding running in different environments are found to be reliable, this could make a difference in using controls in VBA.
Thank you so very much, I really appreciate that. I do remember that library and should have mentioned it during the video. I realize some systems do not have that library and for sure it will help a lot. I actually try not to create templates that require libraries but sometimes the solutions are so nice that we are forced to add additional libraries. Thanks again for your feedback and support.
This needs more testing and when sending attach the file dependenies to the pachage in case they are missing. Sub EnsureActiveXControlsRegistered() Dim controls As Collection Set controls = New Collection ' Add required controls to the collection controls.Add "MSCOMCTL.OCX" controls.Add "COMCTL32.OCX" controls.Add "FM20.DLL" controls.Add "MSCOMCT2.OCX" controls.Add "RICHTX32.OCX" Dim control As Variant For Each control In controls RegisterActiveXControl control Next control End Sub Sub RegisterActiveXControl(controlName As String) Dim controlPath As String Dim fso As Object Dim fileExists As Boolean Dim versionInfo As String Dim systemFolder As String On Error GoTo ErrorHandler ' Determine the correct system folder (32-bit or 64-bit) If Is64BitOS() Then systemFolder = Environ("SystemRoot") & "\SysWOW64\" Else systemFolder = Environ("SystemRoot") & "\System32\" End If ' Path to the control controlPath = systemFolder & controlName ' Create FileSystemObject to check file existence Set fso = CreateObject("Scripting.FileSystemObject") fileExists = fso.FileExists(controlPath) If fileExists Then ' Get the file version (if applicable) versionInfo = GetFileVersion(controlPath) ' Check if the version is adequate (example check for MSCOMCTL.OCX) If controlName = "MSCOMCTL.OCX" And versionInfo < "6.1.98.16" Then ' Register the OCX file RegisterOCX controlPath Else ' Register the control file RegisterOCX controlPath End If Else MsgBox controlName & " not found. Please ensure it is available in the correct system folder.", vbExclamation End If Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical End Sub Function GetFileVersion(filePath As String) As String Dim fso As Object Dim file As Object Dim version As String On Error GoTo ErrorHandler Set fso = CreateObject("Scripting.FileSystemObject") Set file = fso.GetFile(filePath) version = file.Version GetFileVersion = version Exit Function ErrorHandler: MsgBox "An error occurred while getting the file version: " & Err.Description, vbCritical GetFileVersion = "0.0.0.0" End Function Sub RegisterOCX(filePath As String) Dim shell As Object Dim command As String Dim result As Long On Error GoTo ErrorHandler ' Command to register the OCX file command = "regsvr32 /s """ & filePath & """" ' Create Shell object to run the command Set shell = CreateObject("WScript.Shell") ' Run the command result = shell.Run(command, 0, True) If result = 0 Then MsgBox filePath & " registered successfully.", vbInformation Else MsgBox "Failed to register " & filePath & ". Please run the command manually with administrator privileges.", vbCritical End If Exit Sub ErrorHandler: MsgBox "An error occurred while registering the OCX: " & Err.Description, vbCritical End Sub Function Is64BitOS() As Boolean ' Determine if the OS is 64-bit Is64BitOS = Len(Environ("ProgramFiles(x86)")) > 0 End Function
Hi and thanks so much. I am glad you enjoyed the training If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Hi randy, you are really very helpful and thank you very much for your mentorship.. chat gpt can’t find this video, I don’t know why I even shared this link
Hi and thanks so much. What was the question you asked ChatGPT. In it's database it does not have newer training videos. I hope this helps and thanks so much.
HI and thanks, yes it should, Just make sure you have all the required libraries and check for any MISSING libraries within your Reference Libraries. I hope this helps and thanks so much.
HI and thanks very much. It would be great to have you. I also have a freelancer course here www.excelforfreelancers.com/sp/freelancers-academy-masterclass/ I hope this helps and thanks so much.
Hi If you have tried to download this workbook using the links but had a problem please email Angela@ExcelForFreelancers.com . Please supply the email you used along with the workbook you are requesting. Thanks for your patience.
