Shared workbook problem!!!!

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

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 wrote:
>
> 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
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=374868

--

Dave Peterson

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

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 wrote:
>
> 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
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=374868

--

Dave Peterson

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

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



Simon Lloyd wrote:
>
> 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
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=374868

--

Dave Peterson