ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problem With Auto Open (https://www.excelbanter.com/excel-programming/293902-problem-auto-open.html)

Graham[_5_]
Problem With Auto Open
 
Hi,
I have the following code running with Auto Open, but the
procedure does not seem to be working. It looks as if it's
skipping straight to the user form display at the end. I
guess there's something wrong with my "if" statement, but
I don't know what it is. Any help gratefully received!

Thanks
Graham

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

Sheets("READY RECKONER").Visible = True
Sheets("READY RECKONER").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False

Range("CONTRACT_LENGTH").Select
Selection.ClearContents
Range("NO_OF_TEMPS").Select
Selection.ClearContents
Range("DAILY_HOURS").Select
Selection.ClearContents
Range("PAY_RATE").Select
Selection.ClearContents
Range("ENTER_MARGIN").Select
Selection.ClearContents
Range("ENTER_MARKUP").Select
Selection.ClearContents
Range("ENTER_FIXED").Select
Selection.ClearContents

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("READY RECKONER").Visible = False

Sheets("ROLLING PENNY").Visible = True
Sheets("ROLLING PENNY").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False


Range("Q12").Select
ActiveCell.FormulaR1C1 = "1"
Range("S12").Select
ActiveCell.FormulaR1C1 = "1"

Range("A1").Select

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROLLING PENNY").Visible = False

End If

' Display the Ready Reckoner Input Form

ReadyReckoner.Show

Pete McCosh[_5_]
Problem With Auto Open
 
Graham,

my guess would be that

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

is never going to be true. I think you probably meant to
use "UCase"! However, you'll need to capitalise ".XLS" as
well or it still wont match.

Alternatively, put an extra line in your declarations:

Option Compare text

Then you wont need to worry about case sensitivity at all.

Cheers, Pete

-----Original Message-----
Hi,
I have the following code running with Auto Open, but the
procedure does not seem to be working. It looks as if
it's
skipping straight to the user form display at the end. I
guess there's something wrong with my "if" statement, but
I don't know what it is. Any help gratefully received!

Thanks
Graham

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

Sheets("READY RECKONER").Visible = True
Sheets("READY RECKONER").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False

Range("CONTRACT_LENGTH").Select
Selection.ClearContents
Range("NO_OF_TEMPS").Select
Selection.ClearContents
Range("DAILY_HOURS").Select
Selection.ClearContents
Range("PAY_RATE").Select
Selection.ClearContents
Range("ENTER_MARGIN").Select
Selection.ClearContents
Range("ENTER_MARKUP").Select
Selection.ClearContents
Range("ENTER_FIXED").Select
Selection.ClearContents

Sheets("READY RECKONER").Protect
DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("READY RECKONER").Visible = False

Sheets("ROLLING PENNY").Visible = True
Sheets("ROLLING PENNY").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False


Range("Q12").Select
ActiveCell.FormulaR1C1 = "1"
Range("S12").Select
ActiveCell.FormulaR1C1 = "1"

Range("A1").Select

Sheets("READY RECKONER").Protect
DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROLLING PENNY").Visible = False

End If

' Display the Ready Reckoner Input Form

ReadyReckoner.Show


.

NicoB
Problem With Auto Open
 
According to the option (i.e. : Option Compare Text) define in the top of your module the string comparaison of the if...then may not work as you want. In the following code I convert everything to uppercase
Nico
---------------------------------------------------------------------------
If UCase(ThisWorkbook.Name) = "READYRECKONER.XLS" The
Sheets("READY RECKONER").Visible = Tru
Sheets("READY RECKONER").Activat
Sheets("READY RECKONER").Unprotect 'no passwor

Range("CONTRACT_LENGTH")..ClearContent
Range("NO_OF_TEMPS").ClearContent
Range("DAILY_HOURS").ClearContent
Range("PAY_RATE").ClearContent
Range("ENTER_MARGIN").ClearContent
Range("ENTER_MARKUP").ClearContent
Range("ENTER_FIXED").ClearContent

Sheets("READY RECKONER").Protect ,True, True, True 'no passwor
Sheets("READY RECKONER").Visible = Fals

Sheets("ROLLING PENNY").Visible = Tru
Sheets("ROLLING PENNY").Activat
Sheets("ROLLING PENNY").unProtect 'no passwor

Range("Q12").Value = "1
Range("S12").Value = "1
Range("A1").Selec

Sheets("ROLLING PENNY").Protect , True, True, True 'no passwor
Sheets("ROLLING PENNY").Visible = Fals
End I

'Display the Ready Reckoner Input For
ReadyReckoner.Show

Frank Kabel
Problem With Auto Open
 
Hi Graham
change the line
If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

to
If UCase(ThisWorkbook.Name) = "READYRECKONER.XLS" Then


--
Regards
Frank Kabel
Frankfurt, Germany


Graham wrote:
Hi,
I have the following code running with Auto Open, but the
procedure does not seem to be working. It looks as if it's
skipping straight to the user form display at the end. I
guess there's something wrong with my "if" statement, but
I don't know what it is. Any help gratefully received!

Thanks
Graham

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

Sheets("READY RECKONER").Visible = True
Sheets("READY RECKONER").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False

Range("CONTRACT_LENGTH").Select
Selection.ClearContents
Range("NO_OF_TEMPS").Select
Selection.ClearContents
Range("DAILY_HOURS").Select
Selection.ClearContents
Range("PAY_RATE").Select
Selection.ClearContents
Range("ENTER_MARGIN").Select
Selection.ClearContents
Range("ENTER_MARKUP").Select
Selection.ClearContents
Range("ENTER_FIXED").Select
Selection.ClearContents

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("READY RECKONER").Visible = False

Sheets("ROLLING PENNY").Visible = True
Sheets("ROLLING PENNY").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False


Range("Q12").Select
ActiveCell.FormulaR1C1 = "1"
Range("S12").Select
ActiveCell.FormulaR1C1 = "1"

Range("A1").Select

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROLLING PENNY").Visible = False

End If

' Display the Ready Reckoner Input Form

ReadyReckoner.Show

Dave Peterson[_3_]
Problem With Auto Open
 
One more option (I like Option Compare Text, though):

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then
to
If LCase(ThisWorkbook.Name) = lcase("READYRECKONER.xls") Then

or even
If StrComp(ThisWorkbook.Name, "readyreckoner.xls", vbTextCompare) = 0 Then

Graham wrote:

Hi,
I have the following code running with Auto Open, but the
procedure does not seem to be working. It looks as if it's
skipping straight to the user form display at the end. I
guess there's something wrong with my "if" statement, but
I don't know what it is. Any help gratefully received!

Thanks
Graham

If LCase(ThisWorkbook.Name) = "READYRECKONER.xls" Then

Sheets("READY RECKONER").Visible = True
Sheets("READY RECKONER").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False

Range("CONTRACT_LENGTH").Select
Selection.ClearContents
Range("NO_OF_TEMPS").Select
Selection.ClearContents
Range("DAILY_HOURS").Select
Selection.ClearContents
Range("PAY_RATE").Select
Selection.ClearContents
Range("ENTER_MARGIN").Select
Selection.ClearContents
Range("ENTER_MARKUP").Select
Selection.ClearContents
Range("ENTER_FIXED").Select
Selection.ClearContents

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("READY RECKONER").Visible = False

Sheets("ROLLING PENNY").Visible = True
Sheets("ROLLING PENNY").Select
Sheets("READY RECKONER").Protect
DrawingObjects:=False, Contents:=False, Scenarios:=False


Range("Q12").Select
ActiveCell.FormulaR1C1 = "1"
Range("S12").Select
ActiveCell.FormulaR1C1 = "1"

Range("A1").Select

Sheets("READY RECKONER").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("ROLLING PENNY").Visible = False

End If

' Display the Ready Reckoner Input Form

ReadyReckoner.Show

--

Dave Peterson


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com