sheet event wait for another event?

  • Thread starter Thread starter oldyork90
  • Start date Start date
O

oldyork90

I have an "in local workbook" hyperlink on sheet "employee roster". The roster's hyperlink event fires when a selection is made. It writes a cell value on the hyperlink's destination sheet, "employee report"

In the employee report sheet activate event I read that value written by the
roster's hyperlink event. Problem is activate event goes first.

Is there a way to sleep the activate event, and wait for the hyperlink event to finish?
 
Is there a way to sleep the activate event, and wait for the hyperlink event to finish?
Click to expand...

I tried this and .. no go

Dim newHour As Variant: newHour = Hour(Now())
Dim newMinute As Variant: newMinute = Minute(Now())
Dim newSecond As Variant: newSecond = Second(Now()) + 5
Dim waitTime As Variant: waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
 
I have an "in local workbook" hyperlink on sheet "employee roster".
The roster's hyperlink event fires when a selection is made. It
writes a cell value on the hyperlink's destination sheet, "employee
report"

In the employee report sheet activate event I read that value written
by the roster's hyperlink event. Problem is activate event goes
first.

Is there a way to sleep the activate event, and wait for the
hyperlink event to finish?
Click to expand...

Why do you need to read the value in the _Activate event?

What happened with my suggestion to write the value to a defined name?
Then in the report sheet you can retrieve it in a cell via...

=CallerID

...so it automatically appears when the sheet is activated.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Optionally, if you require the caller on the roster sheet be written to
a specific cell on the report sheet...

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Sheets("Report").Range("CallerID") = Target.Parent
End Sub

...which assumes the cell is named "CallerID", and it has local scope.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Optionally, if you require the caller on the roster sheet be written to

a specific cell on the report sheet...



Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Sheets("Report").Range("CallerID") = Target.Parent

End Sub



..which assumes the cell is named "CallerID", and it has local scope.
Click to expand...
I did write information. I did this on the sheet containing the hyperlink cell:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim oWs As Worksheet: set oWs = openWorksheetByCodeName(SHEET_IT_OWNER)
oWs.Range("from_hyper_link").Value = Target.Name

Set oWs = Nothing

End Sub

Not the same info but same idea. Since event management doesn't look like a good idea I'll try writing the report from the followhyperlink event itself.
 
That worked. The report can be written from the hyperlink caller's event.

I tried leaving information on the caller and target sheets for the report's activate event to read and work with. Neither worked. The information just isn't ready until the activate event is done.

Thank you for your help.
 
That worked. The report can be written from the hyperlink caller's
event.

I tried leaving information on the caller and target sheets for the
report's activate event to read and work with. Neither worked. The
information just isn't ready until the activate event is done.

Thank you for your help.
Click to expand...

Glad you got it working! Glad to be some help toward that end...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top