运行时错误438,尝试将用户窗体上的图像保存到工作表时发生。

huangapple go评论95阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年5月29日 10:00:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76354285.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定