Inputing Userform data into spreadsheet

[Bern]

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]

Hi Bern,

[vba] 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 With[/vba]That 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]

Bern

You've also created a cross post.

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]

[vba]Private Sub cmdadd_Click()
With Range("J11").Value = date1.Text & ": " & Comment.Text & _
IIf(Len(.Text) > 0, vbLf & .Text, "")
.WrapText = True
End With
End Sub[/vba]

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]

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]

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]

Thanks Gents. I will watch my netiquette next time, and be a little more patient.

[lucas]

Click here for an explanation of cross-posting

[feathers212]

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:
[vba]Private Sub cmdadd_Click()
With Range("G11")
.Value = date1.Text & ": " & comment.Text & _
IIf(Len(.Text) > 0, vbLf & .Text, "")
.WrapText = True
End With
End Sub[/vba]

[mvidas]

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]

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]

To close a the current userform, on the line following the "End With" you can use:
[VBA]Unload Me[/VBA]

[Bern]

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]

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]

What about double clicking on the project they want to update and up pops the form?

[Charlize]

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]

Here's my shot at it.

[Bern]

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]

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....
[VBA]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[/VBA]

[Bern]

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