I don't have time to tackle all this right now but you have given me much
resource info, and it doesn't look too hard, so I plan to get to it soon.

Thanks again for all your help Tom! Believe it or not, I think I am in
great shape for my deadline and don't have any other open issues!

G

"Tom Ogilvy" wrote in message
...
How hard is a relative question.

In my opinion, it is very easy to create a userform with a combobox that
presents your list of choices - the user click on that, the code writes
the
value to the worksheet and drops the Userform.

However, if you don't know what a userform or combobox is or how to assign
the rowsource property of the userform or how to extract he values and
write
them to the cell, then it would be harder.

A couple of example sources:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


Peter Aiken Articles:
Part I

http://msdn.microsoft.com/library/en...FormsPartI.asp
Part II

http://msdn.microsoft.com/library/en...ormsPartII.asp

A reference:
http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
Aw, shucks! Then, how hard is it to have the dialog box present a
somehow-inherited list, if the choices are in a block of cells on a
spreadsheet? If too hard, how about just hardcoding the list of choices
into the dialog box, as choices you could somehow click on?

Thanks,
G

"Tom Ogilvy" wrote in message
...
You can't really pause a macro to allow the user to work with cells on
a
sheet.

You can stop/end the macro - trust the user to do the right thing
(select
a
value from the dropdown) - the user starts a new macro which is the
second
part of your original macro.


--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
Works like a charm! Tom, you are 'da man'. I hope you don't mind
that
I
used wbSource rather than wkb! I had this left over (commented out)
from
very similar advice I got from Harald on my original post " EXCEL
VBA
question". But somehow all the commands we tried could not quite
get
the
trigger pulled. If you saw that thread and know the magic answer,
kindly
tell us. If it's not worth your time, that's fine.

If I could just get you to look at the " Code Help" thread, that
might
be
my last question for awhile! Lest it be confusing, let me just note
that
the thread refers to some complicated functions, but that has
nothing
to
do
with the issue. Basically, the function produces a dialog box that
allows
me to enter a value for a cell which is blank

Entry = InputBox("Enter a value for selected cell")

However, rather than giving me that dialog box, what I really want
to
do
is
have it allow the user to go directly to the spreadsheet cell and
allow
the
user to choose the cell's contents from a dropdown that is already
installed
in that cell (via data, validation, list). After I use its dropdown
to
make
a choice, the function (and later the macro) should just continue on
its
merry way, just as it does now after inputting to the dialog box.
If
I
have
to hit a "continue" button or something that's OK. I guess I';m
asking
the
macro to stay "on", while I visit a worksheet cell. I assume this
is
do-able.

Thanks,
Grace



"Tom Ogilvy" wrote in message
...
myFilename is built from this line

myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

so it has some name like abcdefgh.*

That is why you get a subscript out of range.

Instead, change you code like this:

Dim myFilename As Variant
Dim wkb as Workbook '<== added line
myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

'MsgBox myFilename ' **** to see what it is looking for
before
it
bombs

Workbooks.OpenText Filename:=myFilename, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array( Array(0, 1), Array(7, 3), Array(17, 1), _
Array(32, 1), Array(40, 1),Array(48, 1)), _
TrailingMinusNumbers:=True
set wkb = Activeworkbook ' <== Added Line
Application.Goto Reference:="R14C1" ' looks like relative
reference
for
cell
A14
For i = 1 To 1400 '1400 is max possible, if have blocks of
about
47
accts each and 65,000 rows
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then GoTo l_pool
ActiveCell.Resize(13).EntireRow.Delete

Next i
l_pool: Rem

Selection.End(xlUp).Select ' is this necessary?
Application.Goto Reference:="R1C1" ' guess this is cell A1

x = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1:f" & x).Select

Application.Calculation = xlCalculationManual ' Turn recalc off
since
this
seems to be just before macro slows down

Selection.Copy

wbTarget.Activate

Sheets("TwtdROR").Select
Application.Goto Reference:="R1C1"
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Workbooks(myFilename).Close SaveChanges:=False
wkb.close SaveChanges:=False ' <== Added Line

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn
about
six
source files and want to close them all, sometimes only after
mere
copying
of data (which doesn't change the file, if that matters when you
try
to
close it, e.g., "save changes?"), sometimes after much
processing
of
the
data before the copying (which does change the file). The
pasting
is
into
the wbTarget calling file, where the macro resides.

The error message I get, assuming I have dim'd myFilename as a
Variant,
is
"subscript out of range". I get it on the command that says to
close
the
file without saving.

If I dim it as a workbook, as many have suggested, I get an
error
message
before I can even open the source file (which I try to on the
next
command)
. It says "object variable or with block variable not set"
right
at
the
point where I am trying to name the file as myFilename (I have a
naming
convention), in anticipation of, on the next command, opening
it.
Since
I
can't get past this point (if I dim it as a workbook), I can't
tell
if,
somehow, I would be able to use the current code to close it
without
saving.
Perhaps it would have worked. But if I can't open the file, it
is
moot
to
wonder how to later close it!

Thanks,
G

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
Grace,
What is your situation?

Situation1 is you have TWO files opened, fileONE with this
macro
and
fileTWO
you referred as wbTarget
and you want to open fileTHREE clear some unwanted data in it,
then
copy
the
remaining data to wbTarget
and close fileTHREE without saving so that you can have the
unchanged
version of fileTHREE.

Situation2 is you have fileONE with this macro and you
referred
as
wbTarget
opened,
and you want to open fileTWO clear some unwanted data in it,
then
copy
the
remaining data to wbTarget
(fileONE) and close fileTWO without saving so that you can
have
the
unchanged version of fileTWO.

with Situation2 your code should work
with Situation1 use Debug.Print myFilename
and see it is your "myFilename"
what is the error you are getting?
Cecil

"Grace" wrote in message
...
Sorry CF, but I cannot tell where you entered your suggested
way
to
close
the files without saving. Once again, that is ALL I need
now.

Kindly clarify what your answer was. it looks like you just
copies
my
macro
in your answer. I assume you changed something but cannot
tell
what
that
was.

Thx,
Grace