Debra Dalgleish-help with worksheet data entry

K

kev

Hi Debra,

I browsed your website and found this new data entry
worksheet(http://contextures.com/xlForm02.html) which was very good and
i showed my boss.He liked the idea of it and thus asked me to work on
it.I managed to add a few more fields and it worked fine.

I have a cell named "Documentation to support requirement" at B11 and
its corresponding text box at D11.My problem now is that at D11, i need
two kind of controls one is drop down list box for document type and
another control is text box for document name.How do i achieve this? i
need both of this control's answer to be combined into one string(one
cell) not in different cells.
i hope u can understand my request.if you can provide me the modified
coding it would be great.
i am really poor in coding.:(

If query one is solved, my ext question will be how can i modify it to
allow multiple entries into singel cell. i might need more than one doc
type n doc name.

pls help, it is really urgent. i was supposed to complete this before
new year but since they saw the new idea, they extended the dateline.i
m moving on to new job thus i need to complete this and ensure it runs
error free.

p/s: if any other MVP/user understand my request, please respond.


Thanks a lot in advance
 
D

Debra Dalgleish

You could add a calculation in a hidden row on the worksheet, where you
combine the values in the document type and document name cells.
For example, in cell D14, enter: =D10 & "; " & D12
Hide row 14.

In the code, add cell D14 to the cells that are being copied, instead of
copying D10 and D12.

If users need to add multiple entries, you could create another couple
of cells for data entry, e.g. Doc2 Type and Doc2 Name. In the hidden
cells, combine the data from all cells.
 
K

kev

Hi Debra,

I tried the first portion and it worked great. However there is one
drawback in which after we click add to database, the D10 and D12 cells
are not cleared as it is not specified in the mycopy range.

Can you please tell me what formula do we use for multiple entries?
The formula you gave works for one pair of doctype n doc name.
Please teach me the syntax.(i know this is way too simple but i admit i
am pretty bad in this)

=D11&":"&F11 &chr(10)& D13&":"&F13 ( i tried this but its giving me an
error).

Thanks a lot.
 
B

Bob Phillips

Just add

.Range("D10,D12").ClearContents

after the End With in this portion of the code.
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End WithYou shouldn't add multiple entries to one cell, you should treat
these as new items, maybe using the same id, so the user would add them one
at a time and they would get a new line on the target sheet.

-- HTH
Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kev said:
Hi Debra,

I tried the first portion and it worked great. However there is one
drawback in which after we click add to database, the D10 and D12 cells
are not cleared as it is not specified in the mycopy range.

Can you please tell me what formula do we use for multiple entries?
The formula you gave works for one pair of doctype n doc name.
Please teach me the syntax.(i know this is way too simple but i admit i
am pretty bad in this)

=D11&":"&F11 &chr(10)& D13&":"&F13 ( i tried this but its giving me an
error).

Thanks a lot.
Click to expand...
 
K

kev

Hi Bob,

Thanks a lot for the feedback, it solved my woes. But if it is done as
your suggestion below, it will create many lines with same repeating
facts except for the changes in doc name. How will i be able to
compress it into one line?(i need it to serve as a master sheet).so
when i look at a particular line, i know that this particular "law" has
corresponding 3 or 4 documents rather than to sort it by id later. And
also what if at a particular time, the user has to input lets say 3
documents, do they need to fill the entire form three times?

Do correct me if i am wrong.If you have a solution, pls post me the
details plus coding if any(i m pretty bad at coding).

Thanks a lot.
Bob said:
Just add

.Range("D10,D12").ClearContents

after the End With in this portion of the code.
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End WithYou shouldn't add multiple entries to one cell, you should treat
these as new items, maybe using the same id, so the user would add them one
at a time and they would get a new line on the target sheet.

-- HTH
Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
B

Bob Phillips

In the database, you definitely should NOT compress it into one line, it
makes data maintenance so difficult. If you want to see it all together, you
should create a view of the data, maybe by filtering the id.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Gord Dibben

kev

Syntax change...................

=D11&":"&F11 &CHAR(10)& D13&":"&F13

But I think you should listen to Bob about not compressing multiples into one
line.


Gord Dibben MS Excel MVP
 
Top