working with 2 or vba's on the same sheet

C

confused deejay

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
 
J

Joel

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
 
C

confused 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
 
D

Dave Peterson

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????
 
C

confused deejay

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)
 
D

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 said:
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)
Click to expand...
 
C

confused deejay

thank you thank you and thank you, you are a diomand
--
deejay

without taking over your life and i swear this is the last thing could you
take a look at another post for me you really are a diomand, its listed under
"auto step sum".
again i've seen clips about it but nothin makes sense and you tell me word
for word what i need to do and it all makes sense to me :)

thanks again

deejay
 
C

confused deejay

sorry dave i think your right in an earlier statement i've over stepped myself.
when putting the page together which is a spred sheet to say who has paid
and how much and how much is outstanding having the auto link to websites is
not needed adding a button that says ok would be better can we take the
follow hyperlink out of the VBA please mate
 
D

Dave Peterson

Is this all you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Me.Cells.EntireColumn.AutoFit
Application.EnableEvents = True

End Sub



confused said:
sorry dave i think your right in an earlier statement i've over stepped myself.
when putting the page together which is a spred sheet to say who has paid
and how much and how much is outstanding having the auto link to websites is
not needed adding a button that says ok would be better can we take the
follow hyperlink out of the VBA please mate
Click to expand...
 
C

confused deejay

no sorry i guess i should of looked into in more detail first.
i want auto fit and hyperlink count on same page, but if i need to add on
another vba do i just add it on the bottom of the one i got?


this is what i'm doing... (i think)

page one.. a1 drop down with month "jan" (each month is on a different sheet)
b1 drop down with name "deejay"
c1 enter amount (which will add the amount to the cell in
jan, deejay

sounds simple enough but i'm probably trying to add to much.
the other thing i can't do is auto jump row i.e.
when i've entered 100 under jan, deejay then another amount under jan
city-link i want to be able to go back to jan, deejay using the same drop
down and add 200. that 200 should now show under the 100 in jan, deejay.

does that make sense? auto fit is just to save time and hyperlink jump was
because some websites are on the drop down but realised when i put the
websites and internal hyperlinks on same drop down it got complicated. so i'm
doing 2 seperate lists now
 
D

Dave Peterson

I'm still confused...

But if you wanted a commandbutton from the control toolbox toolbar to do the
hyperlink count, you could use:

Option Explicit
Private Sub CommandButton1_Click()
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 Sub

But that's just a guess...
 
C

confused deejay

i'm gonna try your last VBA see what happens it may work for what i want lol
thanx for all your help dave you've been a diamond.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Trying to run excel vba code when cell value changes from empty to entered value. 1
Junp start my Select Case Macro 12
Is it possible have a message ( visual or Audio ) when the Curser is place in a cell? 4
Change_Event troubles, no error & no work. 6
2 Macros in VBA??? 3
paste special in this vba code? 1

Top