Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem with code - Help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Les Les is offline
external usenet poster
  
Posts: 240
Default Problem with code - Help
Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Problem with code - Help
You had a from in the old workbook that need to be copied into the new
workbook. If the form is a worksheet
1) open both the new and old workbook
2) right click the tab on the bottom of the old workbook form and select
copy. then select the new workbook

If the form is in VBA
1) Right click the form in the VBA Project window and select export. export
to a file
2) Import the file into the new workbook VBA Project window

"Les" wrote:

Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Les Les is offline
external usenet poster
  
Posts: 240
Default Problem with code - Help
Hi Joel, it is within the same workbook, just a new module ?
--
Les


"Joel" wrote:

You had a from in the old workbook that need to be copied into the new
workbook. If the form is a worksheet
1) open both the new and old workbook
2) right click the tab on the bottom of the old workbook form and select
copy. then select the new workbook

If the form is in VBA
1) Right click the form in the VBA Project window and select export. export
to a file
2) Import the file into the new workbook VBA Project window

"Les" wrote:

Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Problem with code - Help
How is frmSelection defined? I don't think the problem is with the new
module, just frmSelection is not defined in the new module.

"Les" wrote:

Hi Joel, it is within the same workbook, just a new module ?
--
Les


"Joel" wrote:

You had a from in the old workbook that need to be copied into the new
workbook. If the form is a worksheet
1) open both the new and old workbook
2) right click the tab on the bottom of the old workbook form and select
copy. then select the new workbook

If the form is in VBA
1) Right click the form in the VBA Project window and select export. export
to a file
2) Import the file into the new workbook VBA Project window

"Les" wrote:

Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Les Les is offline
external usenet poster
  
Posts: 240
Default Problem with code - Help
Hi Joel, the form is doing what i want it to, however if you see in my code
after the last if statement if all are true, it should branch to the Sub
GetLcsdate, but it goes straight to Problem: ??
--
Les


"Joel" wrote:

How is frmSelection defined? I don't think the problem is with the new
module, just frmSelection is not defined in the new module.

"Les" wrote:

Hi Joel, it is within the same workbook, just a new module ?
--
Les


"Joel" wrote:

You had a from in the old workbook that need to be copied into the new
workbook. If the form is a worksheet
1) open both the new and old workbook
2) right click the tab on the bottom of the old workbook form and select
copy. then select the new workbook

If the form is in VBA
1) Right click the form in the VBA Project window and select export. export
to a file
2) Import the file into the new workbook VBA Project window

"Les" wrote:

Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Problem with code - Help
You better signgle step through the old module and new module to see the
difference in the two modules.

Left click on first instruction in code. then press F8 to step through
code. See which item(s) is false in old code. Compare with new code.

I don't see any thing in the code you posted that is dependant on a NEW MODULE

"Les" wrote:

Hi Joel, the form is doing what i want it to, however if you see in my code
after the last if statement if all are true, it should branch to the Sub
GetLcsdate, but it goes straight to Problem: ??
--
Les


"Joel" wrote:

How is frmSelection defined? I don't think the problem is with the new
module, just frmSelection is not defined in the new module.

"Les" wrote:

Hi Joel, it is within the same workbook, just a new module ?
--
Les


"Joel" wrote:

You had a from in the old workbook that need to be copied into the new
workbook. If the form is a worksheet
1) open both the new and old workbook
2) right click the tab on the bottom of the old workbook form and select
copy. then select the new workbook

If the form is in VBA
1) Right click the form in the VBA Project window and select export. export
to a file
2) Import the file into the new workbook VBA Project window

"Les" wrote:

Hi all,

I have been using the code below with no problem and have just moved it to a
new module and now it wont work ??

Any suggestions welcome, i am not a programmer but a dabbler & just cannot
find the problem.

Sub ChkForFiles()
'
Dim sLCS As String, LCS As String, sIhKTL As String, ihKtl As String
Dim sKTL As String, KTL As String
Dim blnIhKtl As Boolean, blnKtl As Boolean, blnLCS As Boolean, ihSh As
Boolean
Dim ktlSh As Boolean


sLCS = "\\zapad01\sapinter\LCS\" & mylcs & ".xls"
blnLCS = Len(Dir(sLCS))

sIhKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTLih & ".xls"
blnIhKtl = Len(Dir(sIhKTL))

sKTL = "\\nv09002\tpdrive\Projects\General\50_Comparisons \KTL's\" &
myKTL & ".xls"
blnKtl = Len(Dir(sKTL))

If
IfSheetExistsTestIH("\\nv09002\tpdrive\Projects\Ge neral\50_Comparisons\KTL's\" & myKTLih & ".xls", "TOOL TRACKING") = True Then
ihSh = True
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\Gene ral\50_Comparisons\KTL's\"
& myKTL & ".xls", "TOOL TRACKING") = True Then
ktlSh = True
End If
End If

If blnLCS = True Then
If blnIhKtl = True Then
If ihSh = True Then
If blnKtl = True Then
If ktlSh = True Then
GetLcsdate**** Goes to Problem if all true ???
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If
Else
GoTo Problem
End If


Problem:


With frmSelection.Label1
.Caption = "ONE OR MORE FILES/SHEETS NOT AVAILABLE:" & vbCrLf &
vbCrLf & _
"LCS File Available : " & blnLCS & vbCrLf & _
"IH KTL File Available : " & blnIhKtl & vbCrLf & _
"Correct WorkSheet : " & ihSh & vbCrLf & _
"KTL File Available : " & blnKtl & vbCrLf & _
"Correct WorkSheet : " & ktlSh & vbCrLf & vbCrLf & _
"Click ""Reset"" to continue..."
.Font.Name = "Arial"
.Font.Size = 10
.Font.Bold = True
.WordWrap = True
.BackColor = RGB(255, 1, 52)
End With

End Sub

--
Les
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with code problem Mekinnik Excel Programming 5 October 12th 07 03:46 PM
Problem with code JeffJ Excel Programming 2 June 11th 07 01:29 AM
Code Problem MarkHear1 Excel Programming 2 May 15th 07 11:21 AM
VB Code Problem Stan Excel Discussion (Misc queries) 6 April 25th 07 01:48 AM
Code problem Rick[_11_] Excel Programming 1 August 8th 03 04:25 AM


All times are GMT +1. The time now is 06:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement