Help with small project for work please

[Icikle]

Hi, got off on the wrong foot with my initial post, all sorted now though.

I have undertaken a project of my own will to try and improve the way we work at my employment. I have uploaded a preliminary workbook to show you what I am trying to achieve.

There is already code there that adds reference numbers and timestamps to the next available row in the sheet. You will also see there are forward and back buttons.

My aim is to have the back button cycle back through the history of reference numbers submitted and when it gets to the last it displays a messagebox saying they cant go any further back.

My aim with the forward button is, after obviously cycling back, they can then cycle forward back to the most recent, but once its at the front displays nothing in the textbox and an error saying cant go any further forward until they submit more.

I can see an obvious problem with cycling is going to be that itll still let the user submit new references even when the latest one is not displayed. So unless theyre on the most recent and the box is empty, it should disable the text box from user input.

I came up with the below but it only seems to go one back and no further.
TextBox1.Value = Range("A" & Rows.Count).End(xlUp).Offset(-1).Value

I hope I have made myself clear as explaining it is more difficult than I anticipated.

Thanks in advance :)

[Rob342]

icikle
There would be an easier way to do this without the need for forward & back buttons, using a simple form, Combo box and dynamic ranges to hold the data.
I your not in a hurray,i will create you a simple one in which you can learn from it it that suits.
Let me know...

Ps have read your initial post......

[Icikle]

I am not in a hurry at all, anything you can do to help me learn would be appreciated. cheers.

[Kenneth Hobs]

I was going to answer this today but did not see it again and now I know why. The reason that I did not answer it earlier was because of the long explanation. That is fine and good sometimes but I did not have time to do it justice earlier. Most don't like to answer a set of questions unless they can answer all the questions. I generally make an attempt to start solving part of it though as was my intent today.

I looks simple enough. What I would suggest though is that you might consider a spin button control rather than two command buttons. It and the two buttons work in a similar manner so it is up to you. A label control could tell the user when the last or first entry is being shown. I like to use label captions to show the user status update messages.

Of course for such a simple project, the Form button does all of that already. http://spreadsheets.about.com/od/datamanagementinexcel/ss/090717_data_form.htm

[snb]

cfr. the attachment

[Icikle]

thanks, weekend now so wont be able to check til i am back at work on monday.

[Icikle]

thanks, this looks great

could anyone give me a description breakdown of the following code so i can better understand what each part does...thanks

Private Sub CommandButton1_Click()
If ComboBox1.ListIndex = -1 Then Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(ComboBox1.Value, Format(Now, "hh:mm"))
ComboBox1.List = Cells(1).CurrentRegion.Value
ComboBox1.ListIndex = -1
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
End Sub

[snb]

Please use code tags !

open the userform:

Private Sub UserForm_Initialize()
' read all values of the table (all rows & all columns) that starts in cell A1 in sheet1.
ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
End Sub


read more on 'currentregion' and 'resize' in the helpfiles in the VBEditor.


Private Sub CommandButton1_Click()
' if the user didn't select anything in the combobox, but entered a new value in the combobox then add this new value to the table in sheet1

If ComboBox1.ListIndex = -1 Then Sheet1.Cells(combobox1.listcount+2).Resize(, 2) = Array(ComboBox1.Value, Format(Now, "hh:mm"))
' reread the table in sheet1 and populate combobox1 with it.
ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
' reset the combobox1
ComboBox1.ListIndex = -1
End Sub



you can rewrite
Private Sub CommandButton1_Click()
with combobox1
If .ListIndex = -1 Then Sheet1.Cells(.listcount+2).Resize(, 2) = Array(.Value, Format(Now, "hh:mm"))
.List = Sheet1.Cells(1).CurrentRegion.Value
.ListIndex = -1
end with
End Sub

[Icikle]

thanks,

ive done some editing to the code to copy entry to clipboard before it submits it to the sheet, and also to automatically cleanse the sheet of any data when the userform is initialized and come up with this:

Private Sub Label2_Click()
If ComboBox1.Value = "" Then MsgBox "Please entere a valid reference number.", vbCritical Else
ComboBox1.SelStart = 0
ComboBox1.SelLength = ComboBox1.TextLength
ComboBox1.Copy

If ComboBox1.ListIndex = -1 Then Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(ComboBox1.Value, Format(Now, "hh:mm"))
ComboBox1.List = Cells(1).CurrentRegion.Value
ComboBox1.ListIndex = -1

End Sub

Private Sub UserForm_Initialize()
Sheet1.Range("A2:B60").ClearContents
ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
End Sub


Obviously you will see I have changed the button to a label cos buttons are ugly to me lol.

What I now wish to do is whenever a user uses the combobox to change to a previous entry, it automatically copies to clipbaord and displays a message to tell the user this. Thanks in advance.

[snb]

to copy entry to clipboard before it submits it to the sheet
A quoi bon ?

[Icikle]

Excuse e' moi?

if youre basically saying i could use the code ive already done to copy to clipboard to solve the further problem I asked you to solve, you are correct sir...I am dumb :P

[snb]

If you only use a combobox to enter values, you'd better use a textbox instead.

in that case this suffices:
Private Sub UserForm_Initialize()
Sheet1.Range("A2:B60").ClearContents
End Sub


Private Sub Textbox1_beforeUpdate()
if textbox1.Text<>"" then sheets1.cells(rows.count).end(xlup).offset(1).resize(.2)=array(textbox1.Tex t,format(now,"hh:mm"))
textbox1.Text=""
end sub

[Icikle]

appreciated...how do I set this thread to soved?