Help with value from userform

[mercmannick]

hi peeps

can someone show me how to make the value from a userform it has 2 textboxes one for week no and one for day no (week 30 day 5)

and make it change this line of code

strMyBook = "I:\IMF stage starts wk 30.5.xls"

to

strMyBook = "I:\IMF stage starts wk value1.value2.xls"

thanks

Merc

[Norie]

merc

Is this what you mean?

strMyBook = "I:\IMF stage starts wk " & Textbox1.Value & "." & Textbox2.Value & ".xls"

[mercmannick]

how do i incorparate this with the userform in my code Norie



Merc

[Norie]

merc

I don't quite understand what you mean by 'incorporate' it with the userform.

The code I posted should take the values for the textboxes and use them to create the required string for the filename.

[mercmannick]

Sub GetRedCells()
Dim strMyBook As String
Dim cell As Range
Dim TempBook As Workbook
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
    Set TempBook = Workbooks.Add
UserForm1.Show
'Change line below to suit
   ' strMyBook = "I:\IMF stage starts wk 30.5.xls"
    strMyBook = "I:\IMF stage starts wk " & TextBox1.Value & "." & TextBox2.Value & ".xls"
    Workbooks.Open Filename:=strMyBook
    Range([A1], [IV1].End(xlToLeft)).Copy Destination:=TempBook.Sheets(1).Range("A1")
' Change Criteria1 to suit
    Range("D:D").AutoFilter Field:=4, Criteria1:="S03E"
For Each cell In Range("E:E").SpecialCells(xlCellTypeVisible)
    If cell.Interior.ColorIndex = 3 Then _
    cell.EntireRow.Copy Destination:=TempBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
Next cell
ActiveWorkbook.Close False
    Application.ScreenUpdating = True
Range("A:P").EntireColumn.AutoFit
Range("J1").EntireColumn.AutoFit
   On Error GoTo 0
   Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & " (" & Err.Description & ")"
End Sub

Norie on line where it asks to open file the file changes on a daily basis by week no and day no thn main part of the fil;e allways stays the same



also on Range("D").AutoFilter Field:=4, Criteria1:="S03E"

i would like to get another spinbox or similar to be able to pick from the cells in col D


hope this makes sense

Merc

[Justinlabenne]

Related to the same project?

[mercmannick]

yes

[Justinlabenne]

So which method are you planning to go with?

This post works using {getopenfilename}

but at the end you mention using an inputbox. Now it appears your using a userform where you will be using spin-buttons to select the file name in a way.

Why does the code I posted on MrExcel not work for you. This seems like more trouble than you need to go through.

What if the person who saves the file saves it as wrong date? It would be safer to just go and select it everyday, let your code run and be done with it.

[mercmannick]

yes justin i see what you mean, bt i was trying to create this for my own benefit so i can se it working and learn from this , and yes yours does work superbly, i allways try and learn all the options when doing things then if in future i need to do again if i cant remember then i can go back and look at this one as an example,


Thanks for all your support


Merc