Solved: TextBox Deleter Mystery

[maxhayden]

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.

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

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

Thanks in advance,

Max

[Bob Phillips]

Change the Unload Me to Me.Hide. By unloading the form, there is no textbox any longer.

[maxhayden]

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]

You can still F8.

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

[mdmackillop]

[VBA]For j = 100 To 7 step -1
[/VBA]

[maxhayden]

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]

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]

Can you post your workbook?
If there is only one item to be deleted, avoid the loop and use Find.

[maxhayden]

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]

Password?

[Bob Phillips]

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]

Woops! Sorry.. I forgot it was pw protected... here's another copy

[maxhayden]

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]

This seems to work

[vba]

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

[maxhayden]

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?
[VBA] Debug.Assert j <> 14 [/VBA]

[mdmackillop]

You're comparing Text with Numeric
[VBA]
If Cells(j, 2).Value = y * 1 Then
[/VBA]

[mdmackillop]

[VBA]'Subtask Hunt
y = Task_Deleter.TextBox1.Value
Worksheets("Plan Overview").Columns(2).Find(y * 1).EntireRow.Delete
[/VBA]

[Bob Phillips]

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?
[VBA] Debug.Assert j <> 14 [/VBA]

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

[maxhayden]

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!