Calendar control problems when workbook is shared?

[Legacy 98055]

Hi.
This workbook brings up a calendar whenever the user selects a single cell in a specific range. It works fine as is. However, when the workbook is shared the code fails. The errors are occuring whenever the Left or Top properties of the control are assigned new values.(These values will vary depending on the user's range selection.)
Any ideas?
Thanks,
Tom<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long, c As Integer
If CheckBox1 Then Exit Sub
If Selection.Rows.Count<> 1 Then
Calendar1.Visible = False
Exit Sub
End If
r = Target.Row
c = Target.Column
If c = 3 And r > 6 Then
Calendar1.Value = Now
Calendar1.Refresh
Calendar1.Left = Cells(r, c + 1).Left + 5
If Cells(r, c).Top - (Calendar1.Height / 2) > 79 Then
Calendar1.Top = Cells(r, c).Top - (Calendar1.Height / 2)
Else
Calendar1.Top = 79.5
End If
Calendar1.Visible = True
End If
End Sub</pre>
This message was edited by TsTom on 2002-08-27 13:11

 

[Juan Pablo González]

Tom, do you have a Userform ? or did you embed the control in it ? are you using 2000 or XP ?

 

[brettvba]

Tom, I have the same problem with a workbook of my own, when shared some of the macros don't work, I know you can't edit macros when the workbook is shared but what other functionality does sharing the workbook take away?

Brett

 

[Juan Pablo González]

Ok, I have replicated the problem now, so, let's try with a Userform instead... be right back.

 

[starl]

Brett - if you go into the Excel Help: Workbooks, shared - there's a section on the limitations of shared workbooks.

 

[Legacy 98055]

Hi everybody.
I guess you figured out that the control is embedded. Waiting patiently.......



Starl, I could not find anything in help which might apply to this situation???
This message was edited by TsTom on 2002-08-27 17:45

 

[Juan Pablo González]

Tom, I could get it to work using a Userform, and I embed the Calendar in it. You could use the FormFun examples from Stephen Bullen to not display the caption, and other stuff... the problem I had was locating it exactly under the selected cell...

 

[Legacy 98055]

Thanks Juan.
I'll play around with it using forms.
Thanks again!
Tom