Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Excel macros

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
salonowiec - 14 Mar 2008 10:17 GMT
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 - 14 Mar 2008 11:27 GMT
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
> The questions are probably simple:
>
[quoted text clipped - 4 lines]
>
> Thank you for the hint...
salonowiec - 14 Mar 2008 12:24 GMT
> 1. Rt-click the sheet tab - view code to take you into the sheet module
>
[quoted text clipped - 32 lines]
>
> Regards,

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

If Not vFile = False Then

I'm getting error Type mismatch
Peter T - 14 Mar 2008 12:40 GMT
"salonowiec" <boguslaw.knapik@gmail.com> wrote in message
<snip>
> In 2 - after
> If Not vFile = False Then
> I'm getting error Type mismatch

change
> > vFile = Application.GetOpenFilename(sFileFilter, sTitle)

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 - 14 Mar 2008 13:19 GMT
> "salonowiec" <boguslaw.kna...@gmail.com> wrote in message
>
[quoted text clipped - 22 lines]
> Regards,
> Peter T

It's O.K. now, thank you very much...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.