Excel macros

[salonowiec]

The questions are probably simple:

1 - can I create a macro that - after pointing (clicking, double
clicking) to e.g. B5 carries the pointer to e.g. W23?
2 -I'd like macro "waiting" after ActiveSheet.Pictures.Insert( _
(something like "Browse"). My macro inserts always the same picture.

Thank you for the hint...

 

[Peter T]

1. Rt-click the sheet tab - view code to take you into the sheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

If Target.Address = "$B$5" Then Range("W23").Select
End Sub

2.
In a normal module

Sub PictureInsert()
Dim sFileFilter As String, sTitle As String, sAddr As String
Dim vFile

On Error Resume Next
sAddr = ActiveCell.Address(0, 0)
On Error GoTo 0

If Len(sAddr) = 0 Then
MsgBox "First select cell in which to insert picture"
Exit Sub
End If

sFileFilter = "Picture files, *.bmp;*.jpg;*.gif"
sTitle = "Browse to Picture file to insert in cell : " & sAddr

vFile = Application.GetOpenFilename(sFileFilter, sTitle)

If Not vFile = False Then
ActiveSheet.Pictures.Insert vFile
End If

End Sub

Regards,
Peter T

 

[salonowiec]

1. Rt-click the sheet tab - view code to take you into the sheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
            Cancel As Boolean)

    If Target.Address = "$B$5" Then Range("W23").Select
End Sub

2.
In a normal module

Sub PictureInsert()
Dim sFileFilter As String, sTitle As String, sAddr As String
Dim vFile

    On Error Resume Next
    sAddr = ActiveCell.Address(0, 0)
    On Error GoTo 0

    If Len(sAddr) = 0 Then
        MsgBox "First select cell in which to insert picture"
        Exit Sub
    End If

    sFileFilter = "Picture files, *.bmp;*.jpg;*.gif"
    sTitle = "Browse to Picture file to insert in cell : " & sAddr

    vFile = Application.GetOpenFilename(sFileFilter, sTitle)

    If Not vFile = False Then
        ActiveSheet.Pictures.Insert vFile
    End If

End Sub

Regards,

In point 1 it works wonderful... In 2 - after

If Not vFile = False Then

I'm getting error Type mismatch

 

[Peter T]

In 2 - after
If Not vFile = False Then
I'm getting error Type mismatch
change

to

vFile = Application.GetOpenFilename(sFileFilter, , sTitle)

'' or with named arguments
' vFile = Application.GetOpenFilename( _
FileFilter:=sFileFilter, _
Title:=sTitle)

FWIW, originally my code had the named arguments which I striped out so the
code could fit in one line in the post, oh well....

Regards,
Peter T

 

[salonowiec]

to

vFile = Application.GetOpenFilename(sFileFilter, , sTitle)

'' or with named arguments
' vFile = Application.GetOpenFilename( _
FileFilter:=sFileFilter, _
Title:=sTitle)

FWIW, originally my code had the named arguments which I striped out so the
code could fit in one line in the post, oh well....

Regards,
Peter T

It's O.K. now, thank you very much...