jcoleman25
New Member
- Joined
- Jan 26, 2013
- Messages
- 21
I'm having a hard time getting this collection thing down. What I want this code to do is take the user input as a string and store it for use later when the For-Each code is run. I'm not having any luck so far and its probably because I'm having trouble wrapping my head around the concept.
Obviously once I figure out how to add to the collection I can skip the first folder it asks for and just do the collection. As I told a friend of mine once I got it to search 2 folders- "Its a feature, not a bug."
When I've run it like this it either puts the MultiPath in the collection and then overwrites it with the next...
Or
It just ignores that bit and Multipath remains the string that was entered?
For instance, if I enter folder A in the first user input section, then Z and X in the collection, I get A and X but not Z in the output.
Thanks to Al Chara for helping me with the code you don't see here.
That thread is:
Return Hyperlink for Corresponding File from a List on Worksheet.
Obviously once I figure out how to add to the collection I can skip the first folder it asks for and just do the collection. As I told a friend of mine once I got it to search 2 folders- "Its a feature, not a bug."
When I've run it like this it either puts the MultiPath in the collection and then overwrites it with the next...
Or
It just ignores that bit and Multipath remains the string that was entered?
For instance, if I enter folder A in the first user input section, then Z and X in the collection, I get A and X but not Z in the output.
Code:
Dim Cancel As Boolean
Dim FolderPath, PartNoColumn, FirstPart
Dim Multi As Boolean
Public MultiPaths As Collection
Dim Multipath As String
Sub BOMHyperlinks()
Cancel = False
Multi = False
BH2_Setup
If Cancel = True Then Exit Sub
Add_Links FolderPath, PartNoColumn, FirstPart 'code to search and return hyperlinks
If Multi = True Then
For Each Item In MultiPaths
FolderPath = Multipath
BH4_Add_Links FolderPath, PartNoColumn, FirstPart ' code to search and return hyperlinks
Next
End If
Set MultiPaths = Nothing
End Sub
Sub BH3_MultiPath()
On Error GoTo MultiErrorHandle
MorePaths:
Multipath = InputBox("Please enter the path of the additional folder you wish to search: ")
Set PathCheck = CreateObject("Scripting.FileSystemObject")
Set folder = PathCheck.getfolder(Multipath)
MultiPaths.Add Multipath
Select Case MsgBox("Any More?", vbYesNo)
Case vbYes
GoTo MorePaths
Case vbNo
Multi = True
End Select
Exit Sub
MultiErrorHandle:
Select Case Err.Number
Case 76
If MsgBox("That is not a valid file path." & vbCrLf & "Try again?", vbYesNo) _
= vbNo Then Exit Sub Else: _
Resume MorePaths
Case 5
If MsgBox("Sorry, without a folder I cannot continue." & vbCrLf & _
"Try again?", vbYesNo) _
= vbNo Then Exit Sub Else: _
Resume MorePaths
End Select
MsgBox ("Something unexpected has happened. This program will end." & vbCrLf & _
"Please write down this error number for debugging and program improvement." & vbCrLf & _
"The error number is " & Err.Number)
Cancel = True
End Sub
Sub BH2_Setup()
Dim TrimRange As Range
Dim TrimCell As Range
'get user input
ResumePath:
On Error GoTo ErrorHandle
FolderPath = InputBox("Please enter the path of the folder you wish to search: ")
Set PathCheck = CreateObject("Scripting.FileSystemObject")
Set folder = PathCheck.getfolder(FolderPath)
Select Case MsgBox("Is there another folder that you'd like to search?", vbYesNo)
Case Is = vbYes
On Error GoTo 0
BH3_MultiPath
Case Is = vbNo
ContinueProcedure = True
End Select
ResumeColumn:
On Error GoTo ErrorHandle
PartNoColumn = InputBox("Enter the column that the part numbers are in: ")
Range(PartNoColumn & "1").Select
ResumeFirstPart:
On Error GoTo ErrorHandleRow
FirstPart = InputBox("Enter the row that the first part number is in: ")
Range(PartNoColumn & FirstPart).Select
On Error GoTo 0
Set TrimRange = Range(PartNoColumn & FirstPart & ":" & PartNoColumn & ActiveSheet.UsedRange.Rows.Count)
For Each TrimCell In TrimRange
TrimCell = Trim(TrimCell)
Next TrimCell
Exit Sub
'folder and column error handling
ErrorHandle:
Select Case Err.Number
Case 76
If MsgBox("That is not a valid file path." & vbCrLf & "Try again?", vbYesNo) _
= vbNo Then Cancel = True Else: _
Resume ResumePath
Case 5
If MsgBox("Sorry, without a folder I cannot continue." & vbCrLf & _
"Try again?", vbYesNo) _
= vbNo Then Cancel = True Else: _
Resume ResumePath
Case 1004
If MsgBox("That is not a valid column." & vbCrLf & _
"Try again?", vbYesNo) _
= vbNo Then Cancel = True Else: _
Resume ResumeColumn
End Select
GoTo UnknownError
'row error handling
ErrorHandleRow:
Select Case Err.Number
Case 1004
If MsgBox("That is not a valid row." & vbCrLf & _
"Try again?", vbYesNo) _
= vbNo Then Cancel = True Else _
Resume ResumeFirstPart
End Select
GoTo UnknownError
Exit Sub
'unknown error handling
UnknownError:
MsgBox ("Something unexpected has happened. This program will end." & vbCrLf & _
"Please write down this error number for debugging and program improvement." & vbCrLf & _
"The error number is " & Err.Number)
End SubThanks to Al Chara for helping me with the code you don't see here.
That thread is:
Return Hyperlink for Corresponding File from a List on Worksheet.
