Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Good morning everyone,
can anyone please help? i have records in my table that contain times, when i select a date my macro finds all these times, then puts them into textboxes (1-6) what i need is a way of sorting the values, the way it runs at the moment is where the first record that the date matches, it will put the time into the first textbox, then go through the rest of the records till it has found all date matches. only problem is that the dates are in a random order! thank you, Robert Couchman ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Morning Robert,
As you only have 6 textboxes, a simple bubble sort should suffice. Here is some code, but you will need to determine when to call it, whether you do it whenever you change a textbox, or on a commandbutton or whatever. Dim i As Long Dim j As Long Dim tmp As String Dim fSorted As Boolean For i = 1 To 5 For j = i + 1 To 6 If DateValue(Me.Controls("TextBox" & i).Text) _ DateValue(Me.Controls("TextBox" & j).Text) Then tmp = Me.Controls("TextBox" & i).Text Me.Controls("TextBox" & i).Text = Me.Controls("TextBox" & j).Text Me.Controls("TextBox" & j).Text = tmp End If Next j Next i If your textboxes are named, hopefully they can easily be constructed as I have done above, otherwise you will need to store them in an array and extract from the array, something like Me.Controls(aryTextBoxes(i)).Text -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Robert Couchman" wrote in message ... Good morning everyone, can anyone please help? i have records in my table that contain times, when i select a date my macro finds all these times, then puts them into textboxes (1-6) what i need is a way of sorting the values, the way it runs at the moment is where the first record that the date matches, it will put the time into the first textbox, then go through the rest of the records till it has found all date matches. only problem is that the dates are in a random order! thank you, Robert Couchman ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Hello Bob,
thankyou for the help with the sorting, but i seem to have a slight problem. because not all the cells have times in, when they are sorted, the blank spaces move to the top, so any data is in the last few textboxes. any ideas of how to solve this problem? thank you, Robert Couchman ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Hello Robert,
any ideas of how to solve this problem? Yeah, fix the code! Dim i As Long Dim j As Long Dim tmp As String Dim fSorted As Boolean For i = 1 To 5 For j = i + 1 To 6 If Me.Controls("TextBox" & i).Text = "" Then tmp = Me.Controls("TextBox" & i).Text Me.Controls("TextBox" & i).Text = Me.Controls("TextBox" & j).Text Me.Controls("TextBox" & j).Text = tmp ElseIf Me.Controls("TextBox" & j).Text = "" Then 'do nothing, but stop next bit executing ElseIf DateValue(Me.Controls("TextBox" & i).Text) _ DateValue(Me.Controls("TextBox" & j).Text) Then tmp = Me.Controls("TextBox" & i).Text Me.Controls("TextBox" & i).Text = Me.Controls("TextBox" & j).Text Me.Controls("TextBox" & j).Text = tmp End If Next j Next i Where shall we send the bill? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Robert Couchman" wrote in message ... Hello Bob, thankyou for the help with the sorting, but i seem to have a slight problem. because not all the cells have times in, when they are sorted, the blank spaces move to the top, so any data is in the last few textboxes. any ideas of how to solve this problem? thank you, Robert Couchman ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Dim i As Long
Dim j As Long Dim tmp As String Dim fSorted As Boolean For i = 1 To 5 For j = i + 1 To 6 If DateValue(Me.Controls("TextBox" & i).Text) _ DateValue(Me.Controls("TextBox" & j).Text) _ or me.Controls("TextBox" & i).Text = '' Then tmp = Me.Controls("TextBox" & i).Text Me.Controls("TextBox" & i).Text = _ Me.Controls("TextBox" &j).Text Me.Controls("TextBox" & j).Text = tmp End If Next j Next i Should cause blank values to move to the higher numbered boxes. -- Regards, Tom Ogilvy "Robert Couchman" wrote in message ... Hello Bob, thankyou for the help with the sorting, but i seem to have a slight problem. because not all the cells have times in, when they are sorted, the blank spaces move to the top, so any data is in the last few textboxes. any ideas of how to solve this problem? thank you, Robert Couchman ) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Cheers Bob,
i cant pretend i know what this code does at the moment, but im sure i will figure it out soon. as for the bill.... you could send it to my office, and i will file it with the mail i dont often read :) thanks Bob, Robert Couchman ) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Desaster strikes!!!!
Bob, im afraid even though you have updated that piece of code, it still seems to do the same thing!! i will try .value instead of .text, but i cant see it working!! sorry!! the cheque will bounce now! :) Robert Couchman ) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Tell you what, I'll cut out the middle man and put it straight in the bin
myself, but you might be missing a good laugh<g Bob "Robert Couchman" wrote in message ... Cheers Bob, i cant pretend i know what this code does at the moment, but im sure i will figure it out soon. as for the bill.... you could send it to my office, and i will file it with the mail i dont often read :) thanks Bob, Robert Couchman ) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
I tested this and it works:
Private Sub CommandButton1_Click() Dim i As Long Dim j As Long Dim tmp As String Dim fSorted As Boolean Dim sStr as String, sStr1 as String Dim dti as Date, dtj as Date For i = 1 To 5 For j = i + 1 To 6 sStr = Me.Controls("Textbox" & i) sStr1 = Me.Controls("Textbox" & j) If IsDate(sStr) Then dti = CDate(sStr) Else dti = Date + 5000 End If If IsDate(sStr1) Then dtj = CDate(sStr1) Else dtj = Date + 5000 End If If dti dtj Then Me.Controls("TextBox" & i).Text = _ Me.Controls("TextBox" & j).Text Me.Controls("TextBox" & j).Text = sStr End If Next j Next i End Sub -- Regards, Tom Ogilvy "Robert Couchman" wrote in message ... Desaster strikes!!!! Bob, im afraid even though you have updated that piece of code, it still seems to do the same thing!! i will try .value instead of .text, but i cant see it working!! sorry!! the cheque will bounce now! :) Robert Couchman ) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Hello Tom,
thank you for the advice, but im afraid i have a problem. i cannot use the function DateValue, as i am not using a date to input into the textboxes, it is actually a number (e.g. 12.00 for 12o'clock) so i end up with an error, and if i remove the DateValue function then i end up with my numbers in the last few textboxes. can you please help sort out this problem? Thank you, Robert Couchman ) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
Confusing Robert. You have used code with the datevalue and said it worked
except for the blanks problem. are you actually putting in 12.00 instead of 12:00 Anyway, even using the "." this worked: Private Sub CommandButton1_Click() Dim i As Long Dim j As Long Dim tmp As String Dim fSorted As Boolean Dim sStr As String, sStr1 As String Dim dti As Date, dtj As Date ' // generates random times in the textboxes - just '// used for testing For i = 1 To 6 If Rnd < 0.3 Then Me.Controls("Textbox" & i).Text = "" Else Me.Controls("Textbox" & i).Text = _ Format(Rnd(), "h.mm") End If Next '// remove the above For i = 1 To 5 For j = i + 1 To 6 sStr = Me.Controls("Textbox" & i) sStr1 = Me.Controls("Textbox" & j) If IsDate(sStr) Then dti = CDate(sStr) Else dti = Date + 5000 End If If IsDate(sStr1) Then dtj = CDate(sStr1) Else dtj = Date + 5000 End If If dti dtj Then Me.Controls("TextBox" & i).Text = _ Me.Controls("TextBox" & j).Text Me.Controls("TextBox" & j).Text = sStr End If Next j Next i End Sub -- Regards, Tom Ogilvy "Robert Couchman" wrote in message ... Hello Tom, thank you for the advice, but im afraid i have a problem. i cannot use the function DateValue, as i am not using a date to input into the textboxes, it is actually a number (e.g. 12.00 for 12o'clock) so i end up with an error, and if i remove the DateValue function then i end up with my numbers in the last few textboxes. can you please help sort out this problem? Thank you, Robert Couchman ) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox problem
It worked in my tests, putting the blanks at the bottom.
Just tested it again, works fine. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Robert Couchman" wrote in message ... Desaster strikes!!!! Bob, im afraid even though you have updated that piece of code, it still seems to do the same thing!! i will try .value instead of .text, but i cant see it working!! sorry!! the cheque will bounce now! :) Robert Couchman ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with TAB key and Textbox controls | Excel Programming | |||
Textbox adding problem | Excel Programming | |||
Problem with TextBox & ControlSource - Please Help | Excel Programming | |||
TextBox SetFocus Problem | Excel Programming | |||
Excel textbox problem | Excel Programming |