PDA

View Full Version : Solved: Hello all, a little help needed if possible...



insomnai
01-11-2009, 06:59 AM
To clarify a point before we begin, I don't want you to do this for me, but rather point out where I might learn to do this myself...

I'm new to the forums, and as much as I am searching, each answer is having me ask twice as many questions. If there is a link, or reference somewhere else that provides the answer i'm looking for, then that would be fantastic, but some insight would be a great start if you can help...

I have a particular requirement and what i'm wanting to do is use a form to add the following rather than input directly into a spreadsheet...

A1 = ID number i.e. 1, 2, 3 // automatic on new record being added
B1 = Timestamp // automatic as above

C1 = Name of caller
D1 = Address

button which adds current record to the customer list and start a new record

Ok, here is the more difficult bit, I need to see a filtered set of rows (records) in the form (a subform? is this right?) that allows me to select any record that has been added already and update them accordingly. I'll be eventually looking into colouring the backgrounds of the row depending on it's current activity...

E1 = Current Timestamp // on a button click, i.e. when a call is responded to..
F1 = Current Timestamp // on a button click, i.e. when a call is complete..

With these two record additions / edits above, I'll be refering to two more buttons to change it's status....

Reading all this, I realise that this may be a tall order and I might not get all the answers i'm looking for. To be honest, i would love to know how to do all this myself so links would of course be preferred.

I'm looking at the vbax userformexample form that I got hold of from a link I was given. This gives me a little look into how things are done. I just don't understand how to adapt it to my needs.

Many many thanks for giving this some attention and I hope to hear from you soon.

All the best

Insomnai

lucas
01-11-2009, 11:30 AM
Hi insomnai,

We can start with the first part, the data entry and go from there.

In the excel vbid go to insert and select "userform"

from the control toolbox click on textbox and add your 4 textboxes to the userform.

Then you will want to add two command buttons. One to add the data to the sheet and one to cancel the whole operation.

Double click on the button you are going to use to add data to the sheet and you will enter the code module for the userform and the insertion point will be in the procedure for the command button.

Add this code which finds the last row and adds your textbox values to that row......

Dim LastRow As Object
' Set LastRow = Sheet1.Range("a65536").End(xlUp)
Set LastRow = Range("A" & Rows.Count).End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
MsgBox "One record written to Sheet1"
response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If

simple example attached.

lucas
01-12-2009, 01:48 PM
Craig, please mark this solved if you have moved on to Access so others who are looking to help don't waste time reading through this thread.

insomnai
01-12-2009, 02:54 PM
No problem, thanks for your reply Lucas it's much appreciated.

All the best guys!

Insomnai