Forum Discussion
Load Image from path into Image ActiveX Control in worksheet
In such situations, you should temporarily comment out the line
On Error Resume Next
because with it, you won't be notified of any errors.
You declare img as a String, but a String cannot be False. The line
If img <> False Thencauses a Type Mismatch error.
You should declare img as a Variant. That allows it to be any type, whether Boolean (False) or String (a file path).
In the second place, as far as I know, you cannot change the picture on an existing picture shape. You have to delete the existing picture and insert a new one.
Try this version - make sure that there is a shape (any shape) named img_Photo on Sheet3.
Private Sub img_Browse() Dim img As Variant Dim shp As Shape Dim x As Single Dim y As Single Dim w As Single Dim h As Single Dim xCmpPath As String 'On Error Resume Next img = Application.GetOpenFilename(FileFilter:= _ "Pictures (*.jpg;*.png;*.gif;*.bmp),*.jpg;*.png;*.gif;*.bmp") If img <> False Then Set shp = Sheet3.Shapes("img_Photo") x = shp.Left y = shp.Top w = shp.Width h = shp.Height shp.Delete Set shp = Sheet3.Shapes.AddPicture(img, False, True, x, y, h, w) shp.Name = "img_Photo" xCmpPath = img Sheet1.Range("AE1").FormulaR1C1 = xCmpPath End If End Sub
Like this:
Sub LoadStudentImage()
Dim wsBioData As Worksheet
Dim wsReportCards As Worksheet
Dim picturePath As String
Dim studentName As String
Dim studentRow As Range
Dim studPicShape As Shape
' Set your worksheets
Set wsBioData = ThisWorkbook.Sheets("JSS1BD")
Set wsReportCards = ThisWorkbook.Sheets("JSS1RC")
' Remove the existing picture if it exists
wsReportCards.Shapes("STUDPIC").Fill.Solid
' Get the student name from the linked cell (F2) of the ActiveX ComboBox
studentName = wsReportCards.Range("F2").Value
' Find the student in the BioData sheet (Column B)
Set studentRow = wsBioData.Columns("B").Find(What:=studentName, LookIn:=xlValues, LookAt:=xlWhole)
If Not studentRow Is Nothing Then
picturePath = wsBioData.Cells(studentRow.Row, "F").Value
If Dir(picturePath) <> "" Then
On Error Resume Next
wsReportCards.Shapes("STUDPIC").Fill.UserPicture picturePath
On Error GoTo 0
Else
MsgBox "Picture not found for student: " & studentName, vbExclamation
End If
Else
MsgBox "Student not found in BioData sheet: " & studentName, vbExclamation
End If
End Sub
hi, I am done with the project, I tried to clear the JSS1BD, JSS2BD, JSS3BD, SS1BD, SS2BD, SS3BD in order the leave those sheets empty for entry by the user.
i did that manually and the update combobox macro for each of the sheets has bug as attached. Attached is a sample of SS1BD which I cleared and the updatecombobox bug.
How do I debug it to work perfectly even when the BioData sheets are empty so as not to create bug when the ResetToDefault macro (Macro to clear scores and BioData) is triggered thanksHansVogelaar
Microsoft 365 is a subscription version: you pay a monthly or yearly fee. The Office applications in Microsoft 365 are updated every month.
There is also a version you pay only once: Office 2024. This is currently up-to-date, but it won't get new features.
See this website
Thanks I have sent the workbook to your PM
Or better still, I'll send the workbook to your PM so you would open it just to confirm if such error pops up.
Thanks
Okay, please what's the latest version please and how do I get it?
Or do you suggest I trying installing same office version of mine into these systems to see if the issue is resolved.
But I would like to know the latest version and how I can get it
I don't have these older versions; I am afraid I don't know what causes the problem.
Yes, 2016, 2019 but mine is 2021
Do those computers have a different version of Excel than you have?
Thanks.
I've finished everything, everything is working perfectly on my system, but when I sent it to another system, I got error message as attached in the pics.
I tried about 3 different systems and it's same thing but such error didn't occur when I open it with my system, please what's the cause and probable solution.
Thanks
The macro UpdateComboBox2 mistakenly referred to Sheet3 instead of Sheet4. It should be
Sub UpdateComboBox2() Dim combobox As Object Dim studentRange As Range ' Reference the ActiveX ComboBox Set combobox = Sheet15.ComboBox1 ' Clear the ComboBox combobox.Clear ' Define the dynamic range of students in JSS2BD sheet On Error Resume Next Set studentRange = Sheet4.Range("JSS2STUDENTS") ' Add the students to the ComboBox If Not studentRange Is Nothing Then If studentRange.Count = 1 Then combobox.AddItem studentRange.Value Else combobox.List = studentRange.Value End If End If End Sub- Thanks so much I'll be waiting for your response when you're less busy
I'm busy right now, I'll look at it later today.
- Thanks it worked perfectly except the Jss2 report card sheet (JSS2RC). Students added using the form saves correctly and reflect on the score sheet but not on the report card sheet (combobox) like others.
Others now work perfectly.
After helping to fix that of the JSS2, give me the hint of how you tackled it incase of next time.
Thanks so much I have replied to your private message.
Thanks there's, no error again when it's empty but after clearing the BioData all the combobox in the report card sheets are not updating (no name reflects in the combobox after Students were added using the registration forms) how do I fix this issue. I'll forward a copy of the workbook to your PM if needed sir
thanks HansVogelaar
The name JSS1STUDENTS refers to =OFFSET(JSS1BD!$B$1,1,0,COUNTIF(JSS1BD!$B$1:$B$996,"> ")-1,1)
If there are no students, COUNTIF(JSS1BD!$B$1:$B$996,"> ")-1 evaluates to 0, and this causes the error.
The easiest workaround is to use On Error Resume Next to suppress the error.
Sub UpdateComboBox() Dim combobox As Object Dim studentRange As Range ' Reference the ActiveX ComboBox Set combobox = Sheet1.ComboBox1 ' Clear the ComboBox combobox.Clear ' Define the dynamic range of students in JSS1BD sheet On Error Resume Next ' THIS LINE IS NEW Set studentRange = Sheet3.Range("JSS1STUDENTS") ' Add the students to the ComboBox If Not studentRange Is Nothing Then combobox.List = studentRange.Value End If End Sub- The name ranges of all the BioData sheets are correctly exists and each refer to the respective BioData.
When the BioData contains at least 1 data in the name range, it is captured and no bug occurs but when it's empty, it creates a bug.
I already wrote a macro to clear all the BioData sheet which will enable user to clear all data for next session data input.
I have sent a copy of the workbook to your DM.
Thanks On the Formulas tab of the ribbon, click Name Manager.
Does the defined name SS1STUDENTS still exist, or has it disappeared?
If it exists: what does it refer to?