arjkot
07-03-2015, 11:57 AM
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.
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.