Help with Excel VBA code
 

Hello all,

I was wondering if someone more knowledgeable could give me a tip on
something I want to accomplish in Excel 2003. It is as follows:

I have a command button sized exactly as cell A1. A click on this
button should invoke VBA code that can do the following:

a) Enter the current date in cell B1. The code should be able to
determine the location of the command button and enter the current
date in the next cell over (B1 in this case).

b) The code should then be able to read a URL stored two cells over
(C1 in this case) and launch said URL in Internet Explorer.

Any guidance or a working subroutine that sequentially accomplishes
these two tasks would be greatly appreciated.

Thanks

Arsene

Help with Excel VBA code
 

This code goes in the worksheet module that owns that commandbutton (from the
control toolbox toolbar):

Option Explicit
Private Sub CommandButton1_Click()
With Me.CommandButton1.TopLeftCell
'one column to the right
With .Offset(0, 1)
.NumberFormat = "mmmm dd, yyyy"
.Value = Date
End With
'two columns over
With .Offset(0, 2)
ThisWorkbook.FollowHyperlink Address:=.Value, NewWindow:=True
End With
End With
End Sub



On 09/30/2010 17:33, wrote:
Hello all,

I was wondering if someone more knowledgeable could give me a tip on
something I want to accomplish in Excel 2003. It is as follows:

I have a command button sized exactly as cell A1. A click on this
button should invoke VBA code that can do the following:

a) Enter the current date in cell B1. The code should be able to
determine the location of the command button and enter the current
date in the next cell over (B1 in this case).

b) The code should then be able to read a URL stored two cells over
(C1 in this case) and launch said URL in Internet Explorer.

Any guidance or a working subroutine that sequentially accomplishes
these two tasks would be greatly appreciated.

Thanks

Arsene

--
Dave Peterson

Help with Excel VBA code
 

On Sep 30, 7:44*pm, Dave Peterson wrote:
This code goes in the worksheet module that owns that commandbutton (from the
control toolbox toolbar):

Option Explicit
Private Sub CommandButton1_Click()
* * *With Me.CommandButton1.TopLeftCell
* * * * *'one column to the right
* * * * *With .Offset(0, 1)
* * * * * * *.NumberFormat = "mmmm dd, yyyy"
* * * * * * *.Value = Date
* * * * *End With
* * * * *'two columns over
* * * * *With .Offset(0, 2)
* * * * * * *ThisWorkbook.FollowHyperlink Address:=.Value, NewWindow:=True
* * * * *End With
* * *End With
End Sub

On 09/30/2010 17:33, wrote:





Hello all,

I was wondering if someone more knowledgeable could give me a tip on
something I want to accomplish in Excel 2003. It is as follows:

I have a command button sized exactly as cell A1. A click on this
button should invoke VBA code that can do the following:

a) Enter the current date in cell B1. The code should be able to
determine the location of the command button and enter the current
date in the next cell over (B1 in this case).

b) The code should then be able to read a URL stored two cells over
(C1 in this case) and launch said URL in Internet Explorer.

Any guidance or a working subroutine that sequentially accomplishes
these two tasks would be greatly appreciated.

Thanks

Arsene

--
Dave Peterson- Hide quoted text -

- Show quoted text -

How about ONE button. Select the cell with the url and click it. ie:
click c1 to put the date in b1 and goto

Sub dodateandlaunchurl()
With ActiveCell
.Offset(, -1) = Date
ThisWorkbook.FollowHyperlink Address:=.Value, NewWindow:=True
End With
End Sub

Help with Excel VBA code
 

To go with Don's idea: Is the url already in cell C1? If so, this
would not even require a button.

Using the FollowHyperlink worksheet even handler:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Range("B1").Value = Date
End Sub

Help with Excel VBA code
 

On Oct 1, 9:02*am, dksaluki wrote:
To go with Don's idea: *Is the url already in cell C1? *If so, this
would not even require a button.

Using the FollowHyperlink worksheet even handler:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Range("B1").Value = Date
End Sub

Good but OP wants date in cell to left so this works for any cell with
a hyperlink.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Cells(ActiveCell.Row, ActiveCell.Column - 1) = Date
End Sub

Help with Excel VBA code
 

Cannot thank you guys enough. The solution posted by Mr. Peterson
achieves exactly what I was looking for, although the other code
provided would probably work just as well.

Thank you all so, so much !!!

Arsene

On Fri, 1 Oct 2010 12:03:41 -0700 (PDT), Don Guillett Excel MVP
wrote:

On Oct 1, 9:02*am, dksaluki wrote:
To go with Don's idea: *Is the url already in cell C1? *If so, this
would not even require a button.

Using the FollowHyperlink worksheet even handler:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Range("B1").Value = Date
End Sub

Good but OP wants date in cell to left so this works for any cell with
a hyperlink.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Cells(ActiveCell.Row, ActiveCell.Column - 1) = Date
End Sub