error Icon 400

[celiostat]

Hi all,

I regularly extract a excel sheet from a financial system. I grabbed portions of VBA coding (i am not a coder) on the net so i can do the following smoothly and easily:

A- insert of image from hardrive
B- assign this image a CTRL +F function
C- highlight the row and column of the current active cell to easily read related information on one line

and

D- I recorded a macro to reshaped the file presentation in a click.

those macros are stored on a "extractionsupport.xlsm" file. so when i do a Excel extraction from my financial system in a file called "data.xls", I open the "extractionsupport.xlsm" at the same time containing macro recordings , and launch those macro from and for "data.xls"

A and B work fine, but c doesn't (nothing happens) and D : i got an red cross error icon with the text "400".
Strange thing is that if i launch those macros on the hosted file ("extractionsupport.xlsm"), it works fine.

here is the coding :

Sub InsertPicture()
Dim MyCell As Range
Dim MyPicture As Picture
Dim image$
image = "P:\Procurement\Financials\E.purchasing\e.purchasing admin\e.purchsing banner for excel extract.jpg"
Set MyCell = ActiveCell
MyCell.Select
Set MyPicture = ActiveSheet.Pictures.Insert(image)
With MyPicture.ShareRange
.LockAspectRatio = msoFalse
.Height = MyCell.Height
.Width = MyCell.Width
End With
MyCell.Select
End Sub
Sub OpenFindDialog()
    Dim dFind As Dialog
    Set dFind = Application.Dialogs(xlDialogFormulaFind)
    dFind.Show
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count = 1 Then
   Application.EnableEvents = False
   Union(Target.Offset(1, 0).EntireRow, Target.EntireColumn).Select
   Target.Offset(1, 0).Activate
   Application.EnableEvents = True
 End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    Application.EnableEvents = False
    Union(Target.EntireRow, Target.EntireColumn).Select
    Target.Activate
    Application.EnableEvents = True
 End If
End Sub
Sub mef()
'
' mef Macro
'

'
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Rows("4:4").RowHeight = 28.5
    Rows("4:4").Select
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.RowHeight = 34.5
    Range("B5").Select
    ActiveWindow.FreezePanes = True
    Rows("1:3").Select
    Selection.RowHeight = 21
End Sub

I hope this is clear ...
I don't understand any piece of code... so i am really lost... your help would be much welcomed :)
thank you so much guys !!!

Celiostat

ps : let me know if you need more piece of information ! or the excel file

[celiostat]

Hello guys,

Nobody has an idea ?
Thanks for your help !

[Leith Ross]

Hello cellostat,

When working with different versions of programs there can be problems. Problems like properties or methods that exist in one version but not the other. That is the case here. I have made changes to your code so it will work with either version. In VBA it is rare to have to select something before you can perform an action. You will see an absence of "Select" and "Selection" in the code.

Sub mef()
'
' mef Macro
'

'
    With Rows(1).EntireRow
        .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
    
    Rows("1:3").RowHeight = 21
    
    With Rows("1:3").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        If Val(Application.Version) > 11 Then
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End If
    End With
    
    With Rows(4).EntireRow
        .RowHeight = 34.5
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("B5").Select
    ActiveWindow.FreezePanes = True
        
End Sub

[celiostat]

Hi Leith,

I appreciate your response and help, but I still have the error message ! when trying to run the macro from the "data.xls" sheet !
Anything I could do to help ?

[Leith Ross]

Hello cellostat,

After your last post, I think it best if you post a copy of the workbook for review. It will answer a lot more questions faster.