Button action invoking specific cells

[arjkot]

I am checking documentation of a product and at the end of the process I created close to 200 image files. Now to make things quite easy, I wrote a simple script that would have button. One of the buttons asks user for installation dir of the help. Then I created buttons in loop like htm2, htm3..... so that when each is pressed then two things happen. One the htm page opens where I found mistake and also the image gets inserted in active sheet so that user get all info at one place.

Please look at the code:

Sub a()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range


For i = 2 To 10 Step 1
   Set t = ActiveSheet.Range(Cells(i, 5), Cells(i, 5))
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .OnAction = "htmS"
     .Caption = "htm " & i
     .Name = "htm" & i
   End With
Next i

Application.ScreenUpdating = True

End Sub
Sub htmS()
'// Other declarations above
MsgBox Application.Caller

'//
Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")

ie.Navigate ActiveSheet.Range("F2").Value & ActiveSheet.Range("D2").Value
ie.Visible = True

Application.Wait (Now + #12:00:01 AM#)

Dim FileName As String
FileName = ActiveWorkbook.Path & "\" & ActiveSheet.Range("C2").Value & ".png"

   
ActiveSheet.Pictures.Insert (FileName)

End Sub

My problem is that when I invoke action say htm2 it calls the sub. I
need the sub to understand that now i have pressed htm3. you can see
that I have hard coded : ie.Navigate ActiveSheet.Range("F2").Value &
ActiveSheet.Range("D2").Value and FileName = ActiveWorkbook.Path &
"\" & ActiveSheet.Range("C2").Value & ".png"



I need to get info like this:

(Application.caller).caption or the cell for the button and set
ie.Navigate to F3 and also the png part to C3. So, press button htm2 it
takes info from 2nd row, press htm3 takes info from row 3....so on.



I have just started VBA, hope I am excused for a silly question.

[SamT]

With btn 
            .OnAction = "htmS " & i

Sub htmS(Rw As Long) 
ie.Navigate ActiveSheet.Range("F" & Rw).Value & ActiveSheet.Range("D" & Rw).Value

But if you really want to find problems in your code. Put "Option Explicit" at the top of all your code pages. and on the menu, use Debug >> Compile.

BTW, On the Menu Tools >>Options >> Editor Tab >> Code Settings Frame, Check every Option. On the General Tab, Check Compile On Demand

[SamT]

I would really be interested in seeing the project when you get it working. It sounds like a good entry in our KB

[p45cal]

cross posts:
http://www.mrexcel.com/forum/excel-q...fic-cells.html
http://www.excelforum.com/excel-prog...fic-cells.html

[arjkot]

Hi, Sam

I did this but I got the error that cannot find htmS2. This is obviously as i added suffix i with .onAction.

VBA is trying to invoke htmS2 but the sub it is finding is htmS(Rw as long). I am not sure how argument Rw is helping here. I need to pass info to sub htmS that Rw should go to 2 , then to 3 , then to 4 when I press button htm2, htm3......

Dim i As Integer


For i = 2 To 10 Step 1
   Set t = ActiveSheet.Range(Cells(i, 5), Cells(i, 5))
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .OnAction = "htmS" & i

Sub htmS(Rw As Long)
   ie.Navigate ActiveSheet.Range("F" & Rw).Value & ActiveSheet.Range("D" & Rw).Value

Dim FileName As String
FileName = ActiveWorkbook.Path & "\" & ActiveSheet.Range("C" & Rw).Value & ".png"

[SamT]

Note space between S and "

With btn 
    .OnAction = "htmS " & i

[arjkot]

I found a simpler method and used in my sub:

idea was to get row or cell number of the button that I click and pass that for getting each htm and image. Thanks for help though. That


BR = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row... attached is the sub

Sub htmS()
    '// Other declarations above
    MsgBox Application.Caller
    
    Dim BR As Long
    
    BR = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
    '//
    Dim ie As Object
    Set ie = CreateObject("Internetexplorer.Application")
    
    ie.Navigate ActiveSheet.Range("E" & BR).Value & ActiveSheet.Range("C" & BR).Value
    ie.Visible = True
    
    Application.Wait (Now + #12:00:01 AM#)
    
    Dim FileName As String
    FileName = ActiveWorkbook.Path & "\" & ActiveSheet.Range("B" & BR).Value & ".png"
    
       
    ActiveSheet.Pictures.Insert (FileName)

End Sub

[SamT]