Share via

OLEObjects.Add method minimizes active UserForm and stops the macro in the middle

Anonymous
2021-06-11T06:54:31.037+00:00

I have a UserForm that adds an Image Control to the sheet5, but when the line of code that generates the Image Control runs it adds to the sheet and focus retains in the sheet and the current UserForm4 is minimized with close button and execution stops suddenly.
Private Sub CommandButton1_Click()

Set ws = Worksheets("Sheet5")
'Sheet5.Cells(cloC.Row, cloC.Column + 2).select
' ' MsgBox Sheet5.Cells(cloC.Row, cloC.Column + 3)
'
Left = Sheet5.Cells(cloC.Row, 7).Left
Top = Sheet5.Cells(cloC.Row, 7).Top
Width = Sheet5.Cells(cloC.Row, 7).Width
Height = Sheet5.Cells(cloC.Row, 7).Height
'
'
Dim shpTemp As Shape
ans = "new"

For Each shpTemp In Sheet5.Shapes

' 'MsgBox shpTemp.TopLeftCell
If Not Intersect(Sheet5.Cells(cloC.Row, (cloC.Column + 2)), shpTemp.TopLeftCell) Is Nothing Then
ans = MsgBox("image already uploaded. Do you want to add another image", vbYesNo)
'
End If
Next
'
'
'

If ans = "new" Or ans = vbYes Then
Sheet5.OLEObjects.Add Filename:=Dir(fd.SelectedItems(1)), Link:=True, _
DisplayAsIcon:=True, IconFileName:=Dir(fd.SelectedItems(1)), _
IconIndex:=0, IconLabel:=Dir(fd.SelectedItems(1)), Left:=Sheet5.Cells(cloC.Row, 7).Left, Top:=Sheet5.Cells(cloC.Row, 7).Top, Width:=Sheet5.Cells(cloC.Row, 7).Width, Height:=Sheet5.Cells(cloC.Row, 7).Height

  End If

' ' Unload fd
Set fd = Nothing
MsgBox "Image uploaded"
Sheet5.Cells(cloC.Row, cloC.Column + 3).Value = TextBox3.Text

UserForm4.Show vbModeless
UserForm4.Repaint
TextBox3.SetFocus
ComboBox1.SetFocus//EXECUTES THIS LINE BUT GOES TO SHEET5 AND minimizes THIS USERFORM FORCIBILY
end sub// stops here
focus is not coming to userform4.
i have to enter mail Ids to send mail

Private Sub CommandButton2_Click()
i have code to send email here

end sub

Developer technologies | Visual Basic for Applications

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.