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.
🔥Download 350 Of The Worlds Best Templates Here ► rebrand.ly/350Wkbks_YTPinnedCom
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.
I will be glad you share it. Am having some issues
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
Wow Excellent! Thanks so much for sharing. I do appreciate that
@@ExcelForFreelancers How would we make use of this code? Should this be run on workbook_open() ?
Excellent customer list program…nice tutorial
Thank you so very much, I really appreciate that and glad you enjoyed the training Joe
Hi Randy, Really Enjoyed This One - Many Thanks As Always
For sure, you are very welcome Paul and thanks for your continued support
King of excel ❤️
Thank you so very much, I really appreciate that. Very kind of you
King Randy!
Thank you man!
Thank you so very much, I really appreciate that
Wow This looks interesting and super useful too!!!
Thanks so very much Vikram :) I am sure you will love it
Nice and impressive video.
Sir can you help me with inventory management template with fifo, and reorder etc
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.
can't wait !
Great, thanks so much. I am sure you will love this Bill
u my hero dude
Thank you so very much, I really appreciate that
Can’t wait for the end result
Great, thanks so much Aaron, I do appreciate that
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.
Will this work on Excel 2007 ❓
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.
Good video if i join your vba course so can you assist me to become a freeancer in vba,excel
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.
DOWNLOD THIS TEMPLATE LINK NOT WORKING
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.