Phelony
06-08-2011, 04:19 AM
Hi guys :hi:
I'm trying to create a userform which will allow me to pull up archived files without having to sift through the data. I know what I need to do, but it's not working.
This is my first real attempt at a userform so I'm probably just doing something stupid, but a little help would be appreciated.
Private Sub btnreportfetch_Click()
'set archive folder location variables
Dim archfolder As String
archfolder = "Y:\Planning and Performance\Reporting & Management Information\Archive\Regular MI\"
'variables from list boxes and text boxes from userform
'originally set as variables but not needed to be?
'Dim lstreporttype As String - list box
'Dim lstYearSelect As String - list box
'Dim LstMonthSelect As String - list box
'Dim textweek As String - input box
'lookup variables
Dim lookFor As String
Dim rng As Range
Dim col As Integer
Dim found As Variant
'open archiveweek
'triggers from button on userform
If optionweekreport = True Then
Set lookFor = lstreporttype
'range for look up to find proper filename prefix from predetermined lists
'set as C:E to allow copying of successful (if ever) code
Set rng = Sheets("Drop downs").Columns("C:E")
col = 2
On Error Resume Next
filename1 = Application.VLookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox ("The address of " & lookFor & " is " & archfolder & lstYearSelect & "\Weekly\" & filename1 & textweek & ".xls")
End If
End If
End Sub
Apologies for the formatting, it doesn't appear to have carried through correctly.
All of the listboxes and the text box have been named correctly, but I can't get the msgbox to appear at all. :help Once I can get the msgbox up so that the code is working upto that point, I can amend so it performs the retrieval, but I'm really really stuck as I've tampered with everything now to get i functional.
I hijacked the Vlookup code from somewhere else and it did originally work until I started to make the output more complex. The full code is much larger as it contains IF's for all of the Userform buttons, but they aren't conflicting. This one however, just doesn't function despite it being directed correctly and as far as I can see, correctly coded.
Where am I going wrong? :banghead:
Any guidance would be appreciated.
Phel
Edit - the problem I'm experiencing (thought that might help!) is that it's just stalling, it's not returning an error message and it's not returning the msgbox.
I'm trying to create a userform which will allow me to pull up archived files without having to sift through the data. I know what I need to do, but it's not working.
This is my first real attempt at a userform so I'm probably just doing something stupid, but a little help would be appreciated.
Private Sub btnreportfetch_Click()
'set archive folder location variables
Dim archfolder As String
archfolder = "Y:\Planning and Performance\Reporting & Management Information\Archive\Regular MI\"
'variables from list boxes and text boxes from userform
'originally set as variables but not needed to be?
'Dim lstreporttype As String - list box
'Dim lstYearSelect As String - list box
'Dim LstMonthSelect As String - list box
'Dim textweek As String - input box
'lookup variables
Dim lookFor As String
Dim rng As Range
Dim col As Integer
Dim found As Variant
'open archiveweek
'triggers from button on userform
If optionweekreport = True Then
Set lookFor = lstreporttype
'range for look up to find proper filename prefix from predetermined lists
'set as C:E to allow copying of successful (if ever) code
Set rng = Sheets("Drop downs").Columns("C:E")
col = 2
On Error Resume Next
filename1 = Application.VLookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox ("The address of " & lookFor & " is " & archfolder & lstYearSelect & "\Weekly\" & filename1 & textweek & ".xls")
End If
End If
End Sub
Apologies for the formatting, it doesn't appear to have carried through correctly.
All of the listboxes and the text box have been named correctly, but I can't get the msgbox to appear at all. :help Once I can get the msgbox up so that the code is working upto that point, I can amend so it performs the retrieval, but I'm really really stuck as I've tampered with everything now to get i functional.
I hijacked the Vlookup code from somewhere else and it did originally work until I started to make the output more complex. The full code is much larger as it contains IF's for all of the Userform buttons, but they aren't conflicting. This one however, just doesn't function despite it being directed correctly and as far as I can see, correctly coded.
Where am I going wrong? :banghead:
Any guidance would be appreciated.
Phel
Edit - the problem I'm experiencing (thought that might help!) is that it's just stalling, it's not returning an error message and it's not returning the msgbox.