PDA

View Full Version : Inputing Userform data into spreadsheet



Bern
04-10-2007, 11:16 AM
Hello,

I have created a userform to help my colleaugues properly enter enter data into a spreadsheet. The spreadsheet itself has 6 columns:

The information entered into the first 5 collumns will never change, and will be entered by myself. The last collumn entitled "latest activity" is the only one that will expand week to week, as my coleagues are to update the status of that particular project each week.

The goal is to have all the updates visible, with the last on top.:

Example:

Feb 22:Plans with engineering for approval
Jan 26: Plans being drafted
Jan 23, 2007: Budget approved
Jan 22, 2006: Budget has yet to be completed, awating input from Engineering


Initially, I had asked them to put there cursor in the front of the last comment in either the cell or on the formula bar, and hit "alt-enter" to get to the preceeding line, then enter the date and their "latest activity". But it always come back to me as a major mess.

So I am looking to control there input in that last column only by using a user form.

On the user form there are 2 textboxes, 1 for date and the other for the "latest activity:" text. Once the user completes the 2 textboxes on the userform, they click a button, and the new information should be entered in the same cell as the previous activity, but on the line immediatly on top of the previous activity.

I have the user form done, I just need to figure out how to add the data in the user form to a specific place within a specific cell.

Is it possible to do this?

I am a laymen at VBA, so please be patient.

Thanks

Bern

mvidas
04-10-2007, 11:44 AM
Hi Bern,

With Range("A6") 'whatever cell you want to enter the data into
.Value = yourTextBox.Text & ": " & yourLatestActivityTextBox.Text & _
IIf(Len(.Text) > 0, vbLf & .Text, "")
.WrapText = True
End WithThat will modify the given cell (I used A6 in the above example but if you have a range variable, or even ActiveCell, you could use that), and change the name of the textboxes there to the ones on your form. What I'm doing is putting the textbox information in, and if there is already text in the cell, it will move it down a line (the vbLf is a line feed, same as alt-enter).

Matt

Norie
04-10-2007, 11:53 AM
Bern

You've also created a cross post (http://www.mrexcel.com/board2/viewtopic.php?t=266483).:)

I was just about to post an answer of the other forum when I saw this thread.

You still haven't really explained data is structured.

eg is there 1 row per project.

PS You could try attaching a wokrbook here.

Bern
04-10-2007, 12:03 PM
Private Sub cmdadd_Click()
With Range("J11").Value = date1.Text & ": " & Comment.Text & _
IIf(Len(.Text) > 0, vbLf & .Text, "")
.WrapText = True
End With
End Sub

Is what I have entered

cmdadd: is the button name
j11: is the reference cell where the text should be placed
date1: is the first text box
comment: is the second text box

Getting an error mesage on "Invalid or Unqualified reference" with the first line highlited in yellow and the first "text" on the 3rd line highlited in grey.

BTW...thak you very much for your help.

Bern

Bern
04-10-2007, 12:10 PM
Here is the sheet.

Yes I did create a cross post, as I was quickly bumbep to page 4 with 94 views, and no more support.

My apologies if that is a No-No.

Bern

Norie
04-10-2007, 12:15 PM
Bern

It's more an etiquette thing than anything, though some forums are stricter than others.

Anyways, now I can see your data layout I should be able to help further.

But I'm afraid not at the moment, I'm just shooting off.

Perhaps Matt or someone else can help, but I'll check back anyway.

PS If you find your thread bumped then just bump it back.:)

Bern
04-10-2007, 12:19 PM
Thanks Gents. I will watch my netiquette next time, and be a little more patient.

:doh:

lucas
04-10-2007, 12:20 PM
Click here (http://www.excelguru.ca/node/7) for an explanation of cross-posting

feathers212
04-10-2007, 12:42 PM
Go back to Matt's code.....you need to have the .value on the line following the start of the with statement. This worked great for me:
Private Sub cmdadd_Click()
With Range("G11")
.Value = date1.Text & ": " & comment.Text & _
IIf(Len(.Text) > 0, vbLf & .Text, "")
.WrapText = True
End With
End Sub

mvidas
04-10-2007, 12:45 PM
Hi Bern,

Your attached workbook didn't have the actual code in it, so I can only guess. But based on your posting above (with the cmdadd_click code in it), it looks like you are missing a return after the ' With Range("J11")' line

Matt

Bern
04-10-2007, 01:24 PM
Yes the revised code does work. actually it works great.

Is there a way to have the userform disapear once they have entered their data (once they have clicked enter) ?

And can I somehow bold the date (date1). I guess that needs to be done in the userform itself?

Thanks guys.

I did read the cross posting page, and you are right it make sense. I can somtimes be a little impatient, and can take these place for granted. Lessdon learned.

Bern

feathers212
04-10-2007, 01:26 PM
To close a the current userform, on the line following the "End With" you can use:
Unload Me

Bern
04-10-2007, 02:24 PM
Works well, thanks all.

Last question (hopefully) Each row will have a different project, and I will have the same userform for each project (there may be 30-40 on the go). Can I copy and past the button on the main spreadsheet ("click") to each line or row, and the userform will follow...ill just have to change the reference cell?

See attached

feathers212
04-10-2007, 02:50 PM
You could give that a try. I myself would have the user select a cell within the row for the project they need to update. Then have them click on one button at the top of your form. When the button is clicked the program would first figure out the row number for that project and somehow pass the row number to the userform. Then in the cmdadd_click sub, use Range("G" & rownum) to reference the right cell to input the update.

This way, you would not have to recreate a button/cell reference everytime you add a new project line.

Aussiebear
04-11-2007, 03:06 AM
What about double clicking on the project they want to update and up pops the form?

Charlize
04-11-2007, 04:56 AM
The idea of Aussiebear implemented in your workbook. Doubleclick in column B to show what I mean. Better don't use names that are keywords (ie. comments as userform isn't a really good name. I use UF before every userform to be sure that it isn't a keyword that I'm using).

Also try to click on the rows in between the project. ie. the rows that are uneven.

Charlize

Norie
04-11-2007, 07:05 AM
Here's my shot at it.

Bern
04-13-2007, 07:00 AM
Sorry guys, I was away for a couple of days.

Man, you guys are good!

Thank very much.

Question Norie...can the latest comment be added to the top versus the bottom, with the date bolded?

lucas
04-13-2007, 07:24 AM
Quick add to Nories' code...if you use char10 on this line it will get rid of the little square at the end of the line....
Private Sub CommandButton1_Click()
If ComboBox1.ListIndex <> -1 Then
Range("G" & ComboBox1.Column(1, ComboBox1.ListIndex)) = Range("G" & ComboBox1.Column(1, ComboBox1.ListIndex)) & Chr(10) & TextBox1.Value & ":" & TextBox2.Value
End If
End Sub

Bern
04-17-2007, 11:10 AM
I was given some great code for a spreadsheet I was doing (see Norie's attachement above).


Currently the info is added to the end of the cell....I wonder if it is possible to move the data so that it is inserted in the begining of the cell?

Also....Is it possible to bold only the date?

Bern

Thanks

Norie
04-17-2007, 12:26 PM
The first part should be easy.

Private Sub CommandButton1_Click()
If ComboBox1.ListIndex <> -1 Then
Range("G" & ComboBox1.Column(1, ComboBox1.ListIndex)) = TextBox1.Value & ":" & TextBox2.Value & Chr(10) & Range("G" & ComboBox1.Column(1, ComboBox1.ListIndex))
End If
End Sub

The second part regarding the dates is going to be harder.

I'll look into it though and post back if I can get anything.

PS You do realise this isn't the best way to go about this sort of thing?

Norie
04-17-2007, 12:35 PM
Deleted - accidental double post.

Either my computer is playing up or there's a problem with the site.

I favour the former.

Bern
04-17-2007, 01:10 PM
Dont worry about the date, not really required if everything goes on different line. Tks for the code..I will give it a try.

(ok...now to bite down on that big hook!)

I certainly would suspect there is a better way...what do you have in mind?

Bern

Norie
04-17-2007, 01:25 PM
Bern

Well my first thought would be to actually move all the data to an actual database.

Excel can be exploited in some ways to mimic a database but that has it's limitations and normally involves using code like you are.

In database that functionality is built-in, so no need for code.

If you were to go down the database route I would envisage at least 2 tables.

One would hold the 'static' data for each project.

The other a 'history' of the project.

This 2nd table would be the one where the dates and comments would be stored.

And both tables could be linked by a common key, perhaps the project name/id but a lot of database designers think it's better to use an Autonumber field for the key.

This might sound daunting and will probably take time setting up, but once done coule make life a lot easier.

Anyways, I've gone on a bit but perhaps it'll give you some ideas.:)

Note I'm not saying don't use Excel.

A similar structure could be set up using Excel but like I said it would probably involve quite a bit of code.

Bern
04-17-2007, 01:40 PM
Thanks Norie.

The Access thing again. Seems I try to do a lot of things in Excel that I should be doing in Access.

Going to have to take a course in Access (and while I am at it in VBA)

I am gonna run with what I have for now (or should I say what yopu have given me!)...and I am going to find a good access course or book. This is at least the 2nd or 3rd time someone tells me Access might be better.

Thanks again
:beerchug:

Bern
04-17-2007, 01:41 PM
(im going with what he said!)

Deleted - accidental double post.

Either my computer is playing up or there's a problem with the site.

I favour the former.
:beerchug:

Norie
04-17-2007, 04:49 PM
Bern

No problem.:)

If you do go down the Access route post back, in the Access forum perhaps.

But if you want/need to stay with Excel then also post back if you need further help.

If I get the time I'll maybe try to cook something up in Access based on the attachments you've posted so far.

Bern
04-18-2007, 08:02 AM
Thanks Norie.

Ill keep you posted.

Bern

icegg
04-18-2007, 02:03 PM
I have a userform containing 12 checkboxes. I want to put the captions of the checked boxes into an active cell with each caption seperated by a comma, like this "Jan, Mar, May". Can anybody help me with this? Thanks a lot.

Norie
04-18-2007, 04:48 PM
icegg

Could you please start a new thread for this?:)

mdmackillop
04-19-2007, 12:38 PM
Private Sub CommandButton1_Click()
Dim i As Long, txt As String
For i = 1 To 12
If Me.Controls("Checkbox" & i) = True Then
txt = txt & Me.Controls("Checkbox" & i).Caption & ", "
End If
Next
txt = Left(txt, Len(txt) - 2)
Cells(1, 1) = txt

End Sub