hi all i need a very urgent help in userform in excel. my requirement
goes something like this

i have to regenerate a text file 20 times with minor changes in the
text file at each time, to achieve i have written a VBA script (pasted
below) .the current code i have pasted will open a file in excel in
text format and makes changes in the file as written in the code and
then saves it as a text again with a different name. but i want to
make that VBA more interactive and userfriendly, for that what i
thought is to create userform dynamically which will ask the user to
enter the line number in which the text to be modified and text to be
modified. but the primary data of how many line numbers to be modified
will be give by the user in the excel sheet itself. so my userform
should contain the same number of text boxes which is equal to the no
of lines to be modified in the file.


Please help me in this regard

the VBA script which i did is very crude and it goes like this

Sub Modify_file()
'
' Modify_file Macro
' Macro recorded 6/5/2009 by ramki.turaga
'

Dim i, j, k, n, m, x As Integer
Dim bflname As String
Dim nflname As String

m = InputBox("enter total nos rpms", "Total No of different rpms for
which web files to be created")
n = InputBox("enter no of Webs", "No of Web Files to be Created")
For i = 1 To m 'Web number loop
x = InputBox("enter the vale of rpm", "Value of rpm for which file is
tobe generated")
For j = 1 To n 'RPM loop
bflname = "P:\Work\Ramki\Macro\Filetobecopied.cinp"
ChDir "P:\Work\Ramki\Macro"
Workbooks.OpenText Filename:= _
bflname, Origin:=437 _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Range("A10").Select
ActiveCell.FormulaR1C1 = " STDOUT = C175_ehd_" + CStr(x) + "_web"
+ CStr(i) + ".out"
Range("A12").Select
ActiveCell.FormulaR1C1 = "WEBLC =
webload.C175v16_euro_loco_Explicit_" + CStr(x) + "rpm"
Range("A14").Select
ActiveCell.FormulaR1C1 = "STRFILE = web" + CStr(i) + "_NS.unv"
Range("A65").Select
ActiveCell.FormulaR1C1 = "WEB.NUMBER
" + CStr(i)
Range("A73").Select
ActiveCell.FormulaR1C1 = " 3600.0 100.0 " + CStr(x) + "
1 20.0"
nflname = "P:\Work\Ramki\Macro\C175_ehd_" + CStr(x) + "_web" +
CStr(i) + ".cinp"
ActiveWorkbook.SaveAs Filename:= _
nflname, FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.Close
Next j
Next i
End Sub

please any help me to make it more interactive.

You cna use a msgbox to select a range inside the code. See my macro below

Sub Modify_file()
'
' Modify_file Macro
' Macro recorded 6/5/2009 by ramki.turaga
'

Dim i, j, k, n, m, x As Integer
Dim bflname As String
Dim nflname As String

Folder = "P:\Work\Ramki\Macro\"
SourceFile = "Filetobecopied.cinp"

m = InputBox("enter total nos rpms", _
"Total No of different rpms for which web files to be created")
n = InputBox("enter no of Webs", "No of Web Files to be Created")
For i = 1 To m 'Web number loop
x = InputBox("enter the vale of rpm", _
"Value of rpm for which file is to be generated")
For j = 1 To n 'RPM loop
bflname = Folder & SourceFile
Workbooks.OpenText _
Filename:=flname, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True

Set MyRange = MsgBox(prompt:="Select Range", Type:=8)

Range("A" & MyRange.Row) = " STDOUT = C175_ehd_" & CStr(x) & "_web" _
& CStr(i) & ".out"

Range("A12") = "WEBLC = " & _
"webload.C175v16_euro_loco_Explicit_" & CStr(x) & "rpm"
Range("A14") = "STRFILE = web" & CStr(i) & _
"_NS.unv"
Range("A65") = "WEB.NUMBER" & CStr(i)
Range("A73") = " 3600.0 100.0 " & CStr(x) & _
"1 20.0"
nflname = "P:\Work\Ramki\Macro\C175_ehd_" & CStr(x) & _
"_web" & CStr(i) & ".cinp"
ActiveWorkbook.SaveAs Filename:= _
nflname, FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.Close
Next j
Next i
End Sub


"ramki" wrote:

hi all i need a very urgent help in userform in excel. my requirement
goes something like this

i have to regenerate a text file 20 times with minor changes in the
text file at each time, to achieve i have written a VBA script (pasted
below) .the current code i have pasted will open a file in excel in
text format and makes changes in the file as written in the code and
then saves it as a text again with a different name. but i want to
make that VBA more interactive and userfriendly, for that what i
thought is to create userform dynamically which will ask the user to
enter the line number in which the text to be modified and text to be
modified. but the primary data of how many line numbers to be modified
will be give by the user in the excel sheet itself. so my userform
should contain the same number of text boxes which is equal to the no
of lines to be modified in the file.


Please help me in this regard

the VBA script which i did is very crude and it goes like this

Sub Modify_file()
'
' Modify_file Macro
' Macro recorded 6/5/2009 by ramki.turaga
'

Dim i, j, k, n, m, x As Integer
Dim bflname As String
Dim nflname As String

m = InputBox("enter total nos rpms", "Total No of different rpms for
which web files to be created")
n = InputBox("enter no of Webs", "No of Web Files to be Created")
For i = 1 To m 'Web number loop
x = InputBox("enter the vale of rpm", "Value of rpm for which file is
tobe generated")
For j = 1 To n 'RPM loop
bflname = "P:\Work\Ramki\Macro\Filetobecopied.cinp"
ChDir "P:\Work\Ramki\Macro"
Workbooks.OpenText Filename:= _
bflname, Origin:=437 _
, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Range("A10").Select
ActiveCell.FormulaR1C1 = " STDOUT = C175_ehd_" + CStr(x) + "_web"
+ CStr(i) + ".out"
Range("A12").Select
ActiveCell.FormulaR1C1 = "WEBLC =
webload.C175v16_euro_loco_Explicit_" + CStr(x) + "rpm"
Range("A14").Select
ActiveCell.FormulaR1C1 = "STRFILE = web" + CStr(i) + "_NS.unv"
Range("A65").Select
ActiveCell.FormulaR1C1 = "WEB.NUMBER
" + CStr(i)
Range("A73").Select
ActiveCell.FormulaR1C1 = " 3600.0 100.0 " + CStr(x) + "
1 20.0"
nflname = "P:\Work\Ramki\Macro\C175_ehd_" + CStr(x) + "_web" +
CStr(i) + ".cinp"
ActiveWorkbook.SaveAs Filename:= _
nflname, FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.Close
Next j
Next i
End Sub

please any help me to make it more interactive.