Shared workbook problem!!!!

[Simon Lloyd]

Can anyone tel my why this proceedure or command is not available in a
shared workbook, it seems straight forward enough, just cant work out
the bugs,

hope you can help

Simon

Sub Auto_open()

Dim t1 As String

Dim I2 As Integer

For I2 = 1 To 3
t1 = InputBox("Enter Your GBK Login", "Login Verification",
"")
If t1 = "gbksxl04" Or t1 = "gbkdxb02" Or t1 = "gbkmxg04" Or t1
= "gbkaxp02" Or ti = "gbkbxs03" Then
ActiveCell = t1
Call startup
Exit Sub
Else
Worksheets("gbk track").Visible = True
Worksheets("gbk track").Select
ActiveSheet.Range("a2").Select
Selection.Insert Shift:=xlDown
Selection = t1
Worksheets("gbk track").Visible = False
End If
Next

MsgBox "Please try again " & Chr(13) & "Entry not recognised " &
t1

ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub

 

[Dave Peterson]

Take a look at Excel's help for "features unavailable in shared workbooks".

You'll see this:

Unavailable feature: Insert or delete blocks of cells
Alternatives: You can insert entire rows and columns.

So this line:
Selection.Insert Shift:=xlDown
is the line blowing up.
Maybe...
Selection.entirerow.insert
would be ok

And you have a typo.

If t1 = "gbksxl04" Or t1 = "gbkdxb02" Or t1 = "gbkmxg04" Or t1
= "gbkaxp02" Or ti = "gbkbxs03" Then

That gbxbxs03 is checking for ti (Tee-Eye), not T1 (Tee-One).

 

[Simon Lloyd]

David,

Thanks for the speedy reply i will try that as soon as i can.....and of
course thanks for the proof reading....saved me throwing my toys out of
the pram again!!!

Simon

 

[Dave Peterson]

You can add "Option Explicit" at the top of each module and you'll be forced to
declare your variables.

It may sound like it's more work, but you'll find you save time by not searching
for problems like these.

In fact, excel has an option that will turn this on for all new modules. Inside
the VBE:

Tools|Options|Editor Tab
check "Require Variable Declaration"

It's worth the time for new modules and you can always go back to existing and
add both the Option Explicit and the Dim's that you need (when time permits
<vbg>).

 

[Simon Lloyd]

Dave,

just tried the Selection.EntireRow.Insert and it worked a treat.

Just one other question........do you know how to get excel to have no
activecell on start up? my other sub does select a cell before the
whole workbook is open but while the sub i posted earlier is running i
dont want excel to have an activecell i have tried ActiveCell = xlNone
but that just cleared the activecell.

any ideas?

Simon

 

[Dave Peterson]

Can you select a shape or something else instead?

If you don't have a shape on the sheet you want, the only way I know to stop a
cell from being selected is to stop all selections--but you'll still have an
activecell--but the user won't see the cell outlining.

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
.Select
.Protect
.EnableSelection = xlNoSelection
MsgBox ActiveCell.Address
End With
End Sub

I think I'd either just select a cell (A1 is always nice) or one that's way out
of the way.

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
Application.ScreenUpdating = False
Application.Goto .Cells(.Cells.Count), scroll:=False
Application.ScreenUpdating = True
End With
End Sub