Finding the problem with the code that gives the message "The picture is too large and will be truncated" ...

[chazrab]

This only happens when I run this code. The result displays all filtered rows on sheet CARDS2024 that match the value "clothes" in the Memo column 4 that contains
multiple lines of descriptive text. Even after I added Application Enable Events and Application Screen Updating to False at the beginning of the code and rese to True at the end of the code it still gives the message "The picture is too large and wijll be truncated". CoPilot suggested this is a Clear clipboard issue, which I did and it still shows the message. This does no happen with any other code block in this large application - only this one given below. So, it must be someting in the code. Rebooting to recover more memory does not fix it either. I'm at a loss. If using arrays in the code would help I'll do that but have no idea where to insert the array(s) as I have not used arrlays before.

Private Sub cmdFILTERAUTO_Click()
    Dim lastrow As Long, wc, wr As Worksheet
    Set wc = ThisWorkbook.Sheets("CARDS2024")
    Set wr = ThisWorkbook.Sheets("REPORT")
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
   
    wr.Cells.Clear
    lastrow = wc.Cells(wc.Rows.count, 5).End(xlUp).row
    wc.Range("A2").AutoFilter
    wc.Range("$E$1:$EI$" & lastrow).AutoFilter Field:=4, Criteria1:="=*clothes*", Operator:=xlAnd
    wc.AutoFilter.Range.SpecialCells(xlVisible).copy Destination:=wr.Range("A1")
    Application.CutCopyMode = False
    wc.AutoFilterMode = False

    With wr
        If .Range("A1") <> "" Then
            If WorksheetFunction.CountA(.Range("E:E")) = 1 Then
                .Range("E1:E1").Name = "ResultTotal"
                .Range("ResultTotal").Offset(2, 0).Formula = "=SUM(ResultTotal)"
                .Range("ResultTotal").Offset(2, 0).Name = "SumResultTotal"
                .Range("SumResultTotal").NumberFormat = "$#,##0.00"
            Else
                .Range("E1:E" & .Range("E1").End(xlDown).row).Name = "ResultTotal"
                .Range("ResultTotal").End(xlDown).Offset(2, 0).Formula = "=SUM(ResultTotal)"
                .Range("ResultTotal").End(xlDown).Offset(2, 0).Name = "SumResultTotal"
                .Range("SumResultTotal").NumberFormat = "$#,##0.00"
            End If
            UpdateTotalInTextBox2 wr
        End If
    End With
   
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Private Sub UpdateTotalInTextBox2(wr As Worksheet)
    Dim total As Variant
    total = wr.Range("SumResultTotal").value
    If IsNumeric(total) Then
        UserForm1.TextBox2.value = Format(total, "Currency")
    Else
        UserForm1.TextBox2.value = "N/A"
    End If
    UserForm1.Show
End Sub

The code works great except for this quirk. Any insights to solve this surely would be appreciated Thanks for anyone's help.
cr

v

 

[NdNoviceHlp]

Where's the part of the code that puts the range in the image control.... is it an image control on a userform? Dave

 

[chazrab]

Where's the part of the code that puts the range in the image control.... is it an image control on a userform? Dave

Hi Dave - I don't understand your question - there are no images being displayed or copied in this code. Running the code generates the error message in the snip - am I misreading your question

 

[NdNoviceHlp]

The error refers to pictures. Your post has a userform with an picture/image on it. I don't understand your post... your inquiry was about pictures? Perhaps I don't understand how your displaying your range on your userform? Dave

 

[Artik]

When one reads the title and description of the thread, the question that NdNoviceHlp asked immediately comes to mind.
Since you're not hiding anything (no code associated with the image), this indicates a wrong message, although it can actually be linked to the clipboard problem.
Try if anything changes if you fix the end of the procedure like this:

(...)
                .Range("SumResultTotal").NumberFormat = "$#,##0.00"
            End If
          
            Application.OnTime Now, "'UpdateTotalInTextBox2 """ & .Name & """'"
        End If
    End With
 
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

and the beginning of the procedure UpdateTotalInTextBox2 like this:

Private Sub UpdateTotalInTextBox2(wsName As String)
    Dim total As Variant
    Dim wr As Worksheet
  
    Set wr = ThisWorkbook.Worksheets(wsName)
  
    total = wr.Range("SumResultTotal").Value
 (...)

In the code you presented, you can't see how the ListBox control is loaded, as this could also be the source of the problem.

Artik

 

[NdNoviceHlp]

Artik you have better vision/insight than me... a listbox on the userform makes sense as there is a scroll bar that I hadn't noticed. The listbox rowsource must be set to the intended range. In that case, it seems that XL has decided to copy the range as a picture. Perhaps, specifying copy and paste values will fix the "picture" problem. HTH. Dave

Dim rng As Range
Set rng = wc.AutoFilter.Range.SpecialCells(xlVisible)
rng.Copy
wr.Range("A1").PasteSpecial Paste:=xlPasteValues

 

[chazrab]

Artik you have better vision/insight than me... a listbox on the userform makes sense as there is a scroll bar that I hadn't noticed. The listbox rowsource must be set to the intended range. In that case, it seems that XL has decided to copy the range as a picture. Perhaps, specifying copy and paste values will fix the "picture" problem. HTH. Dave

Dim rng As Range
Set rng = wc.AutoFilter.Range.SpecialCells(xlVisible)
rng.Copy
wr.Range("A1").PasteSpecial Paste:=xlPasteValues

It has been a habit for me, probably a bad one, in the past 15 years to assign a Rowource property range that I'm sure will exceed any autofilter results copied to the REPORT sheet which is the sheet that the Rowsource property refers to. Most recently, I set it to =REPORT!A1:G1000, which would certainly cover any autofilter results much less than that. However, as stated, I've been getting a "This picture is too large and will be truncated message" only on the code in this app component. I don't experience that with any other code in this app. That said, is it possible to hard code the Rowsource property in the code to something like =REPORT! A1: lastrow, where lastrow is the exact number of rows in the Autofilter results.? In making the property to extend to 1000 rows use up memory or be a possible cause of the "...will be trucated message then by all means I want to change it to just the number of filtered results in the code, if that's possible. The code I guess would go on the form that displays the results in Listbox1 and not in the code that acturally performs the filter. I don't know. If possible to set the Rowource in code, you tell me. The web shows that , many users have had this same problem as this seems to be a quirk in VBA - but is it? because this does not happen with other code. The question I don't have the answer to is, does Excel use up more memory if the Autofilter results in 20 rows of data and the proprerty range is set to 1000 rows to ensure total coverage of filtered results?

Copilot's answer: The error message you're encountering can indeed be due to the range specified being too large for the RowSource property of the ListBox control. To dynamically set the RowSource property to just the number of rows in the autofiltered results, you can use VBA code to determine the last row of the filtered data and set the RowSource accordingly. Admittedly, I have not yet inserted your code suggesstions earlier in the code. This is just a follow up comment. Thanks to both of you for all your help. Those previous images were not meant to cause any confusion about images and their relationship to the code I posted. Again, thanks to both for all your help. cr Kingwood, Texas

 

[chazrab]

...
This code should be placed in the UserForm's code module, and it will dynamically adjust the RowSource property based on the actual number of rows in the filtered data. This approach ensures that the ListBox only includes the relevant rows, potentially avoiding the "This picture is too large and will be truncated" message.

Give it a try and see if it resolves the issue. If you run into any more quirks or need further assistance, feel free to ask!
CoPilot


Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range

' Set the worksheet
Set ws = ThisWorkbook.Sheets("REPORT")

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Set the range for the ListBox RowSource
Set rng = ws.Range("A1:G" & lastRow)

' Set the RowSource property of ListBox1
Me.ListBox1.RowSource = rng.Address(external:=True)

 

[NdNoviceHlp]

Thanks for the update. Please do let us know how you make out. I've had this similar error in the past however it was related to copying a range, making a picture of it and then inserting it in a frame on a userform (which also had scroll bars). If you're unable to resolve this, I'll look back and see if I can remember how I fixed the problem. Dave

 

[NdNoviceHlp]

Did some look back. It seems I adjusted the control size to fit the range. I'm not sure if this is useful for your project but here's an adaptation. HTH. Dave

Private Sub UserForm_Initialize()
Dim Rng As Range, Tmp As Double, Tstr As String
' Find the last row with data in column A
Set ws = ThisWorkbook.Sheets("sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set the range for the ListBox RowSource
Set Rng = ws.Range("A1:G" & lastRow)
' Set the RowSource property of ListBox1
Me.ListBox1.RowSource = Rng.Address(external:=True)
'Me.ListBox1.Height = Rng.Height
'DoEvents
Me.ListBox1.ColumnCount = 7
Me.ListBox1.Width = Rng.Width
DoEvents
Tmp = Me.ListBox1.Width / Me.ListBox1.ColumnCount
Tmp = Tmp - 1
For cnt = 1 To Me.ListBox1.ColumnCount
Tstr = Tstr & Tmp & ","
Next cnt
Me.ListBox1.ColumnWidths = Left(Tstr, Len(Tstr) - 1)
End Sub