英文:
Runtime error 438 when trying to save an image on a userform to a worksheet
问题
Excel VBA - 运行时错误 '438' 对象不支持此操作。
我已创建一个包含7个文本框和一个图片框的用户表单。这一切都很好,包括上传图像文件。我的问题涉及将这些数据以及来自用户表单的图像上传到Excel工作表中。一旦保存,工作表将被排序,并调整单元格以适应其内容。
如果从文本框上传数据,子程序将运行。
但是,当我将其中一个文本框更改为图像框并运行它时,会收到上面的错误消息。
非常感谢任何建议?
这是我的子程序:
Private Sub CommandSave_Click()
'将表单数据保存到新行并清除
Dim Worksheet As Range
Dim ws As Worksheet
Set ws = ActiveSheet
'将文本框保存到工作表
Dim lr As Long
lr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Cells(lr, "A").Value = Me.TextBox_1.Value
ws.Cells(lr, "B").Value = Me.TextBox_2.Value
ws.Cells(lr, "C").Value = Me.TextBox_1 + TextBox_2.Value
ws.Cells(lr, "D").Value = Me.TextBox_3.Value
ws.Cells(lr, "E").Value = Me.TextBox_4.Value
ws.Cells(lr, "G").Value = Me.TextBox_6.Value
ws.Cells(lr, "H").Value = Me.TextBox_7.Value
ws.Cells(lr, "I").Value = Me.TextBox_8.Value
'将图像保存到单元格
Dim ShpPicture As OLEObject
With ws
Set ShpPicture = .OLEObjects.Add(ClassType:="Forms.Image.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Cells(lr, "F").Left, _
Top:=.Cells(lr, "F").Top, _
Width:=Me.Image_1.Width, _
Height:=Me.Image_1.Height)
End With
With ShpPicture
.objects.PictureSizeMode = 3
.ObjectPicture = Me.Image_1.Picture
End With
'清空文本框
TextBox_1.Value = ""
TextBox_2.Value = ""
TextBox_3.Value = ""
TextBox_4.Value = ""
TextBox_6.Value = ""
TextBox_7.Value = ""
TextBox_8.Value = ""
'按两个字段对数据进行排序
Set SortA = ws.Range("a8:i" & lr)
Sort_Two_columns A & B
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add2 Key:=Range("A8:A" & lr), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add2 Key:=Range("B8:B" & lr), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A8:I" & lr)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'调整单元格以适应文本
ws.UsedRange.EntireColumn.AutoFit
ws.UsedRange.EntireRow.AutoFit
End Sub
英文:
Excel VBA - Runtime Error '438' Object doesn't support this.
I have created a user form with 7 textboxes and a picture box. This all works fine including the upload of the image file.
My problem concerns uploading these data including the image from the userform to an excel sheet. Once saved the sheet is to be ordered and the cells adjusted to fit their content.
The Sub runs if data is being uploaded is from textboxes.
When I run it with one of the textboxes changed to an image box I receive the error message above.
Grateful for any advice?
This is my sub
Private Sub CommandSave_Click()
'Save form data to new row and clear
Dim Worksheet As Range
Dim ws As Worksheet
Set ws = ActiveSheet
'Save Textboxes to Worksheet
Dim lr As Long
lr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Cells(lr, "A").Value = Me.TextBox_1.Value
ws.Cells(lr, "B").Value = Me.TextBox_2.Value
ws.Cells(lr, "C").Value = Me.TextBox_1 + TextBox_2.Value
ws.Cells(lr, "D").Value = Me.TextBox_3.Value
ws.Cells(lr, "E").Value = Me.TextBox_4.Value
ws.Cells(lr, "G").Value = Me.TextBox_6.Value
ws.Cells(lr, "H").Value = Me.TextBox_7.Value
ws.Cells(lr, "I").Value = Me.TextBox_8.Value
'Save image to Cell
Dim ShpPicture As OLEObject
With ws
Set ShpPicture = .OLEObjects.Add(ClassType:="Forms.Image.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Cells(lr, "F").Left, _
Top:=.Cells(lr, "F").Top, _
Width:=Me.Image_1.Width, _
Height:=Me.Image_1.Height)
End With
With ShpPicture
.objects.PictureSizeMode = 3
.ObjectPicture = Me.Image_1.Picture
End With
'Empty Textboxes
TextBox_1.Value = ""
TextBox_2.Value = ""
TextBox_3.Value = ""
TextBox_4.Value = ""
TextBox_6.Value = ""
TextBox_7.Value = ""
TextBox_8.Value = ""
'Sort Data on Two Fields
Set SortA = ws.Range("a8:i" & lr)
Sort_Two_columns A & B
ws.Sort.SortFields.Clearws.Sort.SortFields.Add2 Key:=Range("A8:A" & lr), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add2 Key:=Range("B8:B" & lr), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A8:I" & lr)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Resize cells to fit text
ws.UsedRange.EntireColumn.AutoFit
ws.UsedRange.EntireRow.AutoFit
End Sub
答案1
得分: 2
你应该参考OleObject对象的Object属性...
With ShpPicture
.Object.PictureSizeMode = 3
.Object.Picture = Me.Image_1.Picture
End With
英文:
You should be referring to the Object property of the OleObject object...
With ShpPicture
.Object.PictureSizeMode = 3
.Object.Picture = Me.Image_1.Picture
End With
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论