PDA

View Full Version : Solved: TextBox Deleter Mystery



maxhayden
05-29-2009, 02:38 AM
Hello All,

I have a user form that search columns and deletes the rows if the number in the textbox matches the number in Column B.

It's really simply and straight forward but it doesn't work!!!

I have written the same code in a test sheet using a cell instead of a textbox as the reference and it works - so I must be doing something wrong with specifying the textbox as a number.


Private Sub CommandButton1_Click()
'Main Task Hunt
x = Task_Deleter.TextBox1.Value
If Fix(x) - x = 0 Then
MainTask_Warning.Show
Exit Sub
Unload Me
End If

'Subtask Hunt
For j = 7 To 100
y = Task_Deleter.TextBox1.Value
Worksheets("Plan Overview").Select
If Activesheet.Cells(j, 2).Value = y Then
Rows(j).Delete
End If
Next j

End Sub


Can someone tell me what I'm doing wrong because it is a real mystery to me!

Thanks in advance,

Max:dunno

Bob Phillips
05-29-2009, 04:02 AM
Change the Unload Me to Me.Hide. By unloading the form, there is no textbox any longer.

maxhayden
05-29-2009, 04:34 AM
Hi xld,

That doesn't work. The Unload Me was in the first IF statement that doesn't get statisfied.
I shouldn't have put the 'Main Task Hunt section of code in the post. (Sorry)

It is the 2nd IF statement ('Subtast Hunt) that gets satisfied, but for some reason, it isn't working.

There's something in that j loop that doesn't like something.

It's really difficult to see what is going wrong because you can't F8 through the sub due to the Userform! :(

Bob Phillips
05-29-2009, 04:57 AM
You can still F8.

Are you sure you aren't unloading the userform when you exit that, instead of hiding it?

mdmackillop
05-29-2009, 05:00 AM
For j = 100 To 7 step -1

maxhayden
05-29-2009, 05:02 AM
I've deleted the unload me and Exit sub from the code just to see and it doesn't change anything.

If Fix(x) - x = 0 (i.e. 2.00 - 2.00 = 0) a warning message comes up.

If Fix(x) - x <> 0 (i.e. 2.00 - 2.01 = -0.01) it goes through the j loop.
For some reason, the j loop doesn't recognise when the text box value (eg. 2.01) is equal to Cells(j, 2) (eg. 2.01)

maxhayden
05-29-2009, 05:06 AM
Hi mdmackillop,

I agree the delete should run stepping backwards (although the textbox only has 1 field so it only deletes 1 row per button press)

I have added this to the code but still no joy!

ps. When I try F8ing through, the textbox comes up and I can't F8 any further. :(

mdmackillop
05-29-2009, 05:08 AM
Can you post your workbook?
If there is only one item to be deleted, avoid the loop and use Find.

maxhayden
05-29-2009, 05:17 AM
Here you go.

The fuction I'm working on is in the "Edit Tasks" Button and then the "Task Deleter" Button.

It works for Main Tasks, but not for sub tasks

(Oh.. and its not finished yet either :) )


... I've just throught of a way round it... but its long winded and invasive.

If I first add the textbox1.value to an unused cell. Run the search using that cell, then clear the cell afterwards.. uncool but there you go.

ps.. (I've only been using VBA selftought for about 2 months so apologies if it's a load of rubbish!)

mdmackillop
05-29-2009, 05:25 AM
Password?

Bob Phillips
05-29-2009, 05:26 AM
I've deleted the unload me and Exit sub from the code just to see and it doesn't change anything.

If Fix(x) - x = 0 (i.e. 2.00 - 2.00 = 0) a warning message comes up.

If Fix(x) - x <> 0 (i.e. 2.00 - 2.01 = -0.01) it goes through the j loop.
For some reason, the j loop doesn't recognise when the text box value (eg. 2.01) is equal to Cells(j, 2) (eg. 2.01)

No, I revised that. I was later talking about the form code, how do you exit the form?

maxhayden
05-29-2009, 05:30 AM
Woops! Sorry.. I forgot it was pw protected... here's another copy

maxhayden
05-29-2009, 05:32 AM
Hi xld,

I don't exit the form. I leave it open (for the time being...) because I was I was still working on the sub

Bob Phillips
05-29-2009, 05:39 AM
This seems to work



Private Sub CommandButton1_Click()

'Main Task Hunt

x = Task_Deleter.TextBox1.Value

If Fix(x) - x = 0 Then

MainTask_Warning.Show

Exit Sub

Unload Me

End If


'Subtask Hunt

y = Val(Task_Deleter.TextBox1.Value)
For j = 100 To 7 Step -1

Debug.Assert j <> 14

If Worksheets("Plan Overview").Cells(j, 2).Value = y Then

Worksheets("Plan Overview").Rows(j).Delete
End If
Next j

'Re Sort Subtask numbers
For h = 8 To 100

If Not Int(Cells(h, 2).Value) = Cells(h, 2).Value Then

Cells(h, 2).Value = Cells(h - 1, 2).Value + 0.01
End If
Next h

End Sub

maxhayden
05-29-2009, 05:42 AM
I knew it was going to be something infront of the textbox1.value to make it recognise it as a number.

what does this do?
Debug.Assert j <> 14

mdmackillop
05-29-2009, 05:42 AM
You're comparing Text with Numeric

If Cells(j, 2).Value = y * 1 Then

mdmackillop
05-29-2009, 05:46 AM
'Subtask Hunt
y = Task_Deleter.TextBox1.Value
Worksheets("Plan Overview").Columns(2).Find(y * 1).EntireRow.Delete

Bob Phillips
05-29-2009, 05:50 AM
I knew it was going to be something infront of the textbox1.value to make it recognise it as a number.

what does this do?
Debug.Assert j <> 14

That was some testing code I left in, remove it.

maxhayden
05-29-2009, 05:51 AM
Thanks to both of you!

Both of them work.... Val(xxxx) and xxxx * 1

I knew from the start that it wasn't recognising it as a number because it worked when i referenced it against a cell.

I just didn't know how to make it see it as a number.

Thanks again for your help!