hiya i have been reading up and following suggestions by other users however
this has meant that i am using more then one VBA on my worksheet. which
results in an error when system tries to activate the VBA error message
displays (ambigiuos name detected)

is there a way to get both or, more then one VBA with same name on a sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

also how do you put a VBA into a workbook/ excel so i don't have to copy
paste the same VBA on every page? again this will probably have more then one
VBA in it
--
deejay

Since you can have only one worksheet change macro yo need to test the target
range to detrmine what actions the code should take.

A worksheet change can call another macro, but you need to have a worksheet
change on every sheet that you want it to work on. The macro can have just
three statements

Private Sub Worksheet_Change(ByVal Target As Range)
call common_code(target)
exit sub

"confused deejay" wrote:

hiya i have been reading up and following suggestions by other users however
this has meant that i am using more then one VBA on my worksheet. which
results in an error when system tries to activate the VBA error message
displays (ambigiuos name detected)

is there a way to get both or, more then one VBA with same name on a sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

also how do you put a VBA into a workbook/ excel so i don't have to copy
paste the same VBA on every page? again this will probably have more then one
VBA in it
--
deejay

so how would i write this work_sheet change on one sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

sorry i'm very new to vba actually new to excel too only know what i know
through reading posts on here
--
deejay

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

'always fit the columns
Application.EnableEvents = False
Me.Cells.EntireColumn.AutoFit
Application.EnableEvents = True

If Intersect(Me.Range("A2"), Target) Is Nothing Then
'do nothing
Else
s = Me.Range("B2").Value
Me.Parent.FollowHyperlink Address:=s
End If
End Sub


But did you really want to check to see if the changed cell was A2, but follow
the link in B2????



confused deejay wrote:

so how would i write this work_sheet change on one sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

sorry i'm very new to vba actually new to excel too only know what i know
through reading posts on here
--
deejay

--

Dave Peterson

thanx for your answer just what i wanted :-)

yes i think so, a2 is a dropdown list of websites, b2 activates the
hyperlink when i click on say google on the dropdown rather then clicking the
link on b2 i can hide b2 and the dropdown will jump to said website.
if there is a better way please feel free to let me know :-)

i know this is probably naughty but no one has answered my other question on
different post and i think you would know the answer.

(post) following a hyperlink.
i've seen the question on here and the answer but can't find it now, the
question is how do i follow how many times a hyperlink is activated?
(guessing its not possible for the external hyperlinks that i have set to
auto with the above vba) but i have seperate worksheet to worksheet
hyperlinks that i would like to monitor how many times they were activated.

(just to let you know before you tell me to add another vba! its on the same
worksheet so it will have to add to my already 2 vba's)
--
deejay


"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

'always fit the columns
Application.EnableEvents = False
Me.Cells.EntireColumn.AutoFit
Application.EnableEvents = True

If Intersect(Me.Range("A2"), Target) Is Nothing Then
'do nothing
Else
s = Me.Range("B2").Value
Me.Parent.FollowHyperlink Address:=s
End If
End Sub


But did you really want to check to see if the changed cell was A2, but follow
the link in B2????



confused deejay wrote:

so how would i write this work_sheet change on one sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

sorry i'm very new to vba actually new to excel too only know what i know
through reading posts on here
--
deejay

--

Dave Peterson

Maybe...

Say your list of choices (for A2) is on Sheet2!A1:A### and you've named it
myList.

Then you could use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim myListRng As Range
Dim CounterCell As Range

'always fit the columns
Application.EnableEvents = False
Me.Cells.EntireColumn.AutoFit
Application.EnableEvents = True

If Intersect(Me.Range("A2"), Target) Is Nothing Then
'do nothing
Else
With Me.Range("a2")
If IsEmpty(.Value) Then
'do nothing
Else
Set myListRng = Me.Parent.Worksheets("Sheet2").Range("myList")
res = Application.Match(.Value, myListRng, 0)
If IsError(res) Then
'this shouldn't happen
Else
Set CounterCell = myListRng(res).Offset(0, 1)
If IsNumeric(CounterCell.Value) Then
CounterCell.Value = CounterCell.Value + 1
Else
CounterCell.Value = 1
End If
End If
Me.Parent.FollowHyperlink Address:=Me.Range("B2").Value
End If
End With
End If
End Sub

This adds 1 to the cell to the right of your list. But since it's storing the
value within the workbook, if the user closes without saving, then the count
won't be really updated.

confused deejay wrote:

thanx for your answer just what i wanted :-)

yes i think so, a2 is a dropdown list of websites, b2 activates the
hyperlink when i click on say google on the dropdown rather then clicking the
link on b2 i can hide b2 and the dropdown will jump to said website.
if there is a better way please feel free to let me know :-)

i know this is probably naughty but no one has answered my other question on
different post and i think you would know the answer.

(post) following a hyperlink.
i've seen the question on here and the answer but can't find it now, the
question is how do i follow how many times a hyperlink is activated?
(guessing its not possible for the external hyperlinks that i have set to
auto with the above vba) but i have seperate worksheet to worksheet
hyperlinks that i would like to monitor how many times they were activated.

(just to let you know before you tell me to add another vba! its on the same
worksheet so it will have to add to my already 2 vba's)
--
deejay

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

'always fit the columns
Application.EnableEvents = False
Me.Cells.EntireColumn.AutoFit
Application.EnableEvents = True

If Intersect(Me.Range("A2"), Target) Is Nothing Then
'do nothing
Else
s = Me.Range("B2").Value
Me.Parent.FollowHyperlink Address:=s
End If
End Sub


But did you really want to check to see if the changed cell was A2, but follow
the link in B2????



confused deejay wrote:

so how would i write this work_sheet change on one sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2"), Target) Is Nothing Then
Exit Sub
End If
Dim s As String
s = Range("B2").Value
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

sorry i'm very new to vba actually new to excel too only know what i know
through reading posts on here
--
deejay

--

Dave Peterson


--

Dave Peterson