Need help with Userform

rbi

New Member
Joined
Oct 3, 2009
Messages
7
I'm terribly new to VBA and am struggling. I'm thinking this should be simple but I've searched the questions forum and don't see anything close. (Maybe too basic). I have a sheet with multiple rows of data. I'm trying to create a form that prompts for a line number that the user would like to delete from the form. The line numer comes from the data and is not the rownumber. What I've created sort of works but with quirks. I get the "Invalid Entry" msgbox even when a line is deleted. I'm hoping someone can offer advice & mentoring. Here's userform procedure code. Thanks for giving this a look-see. Ron I.

Private Sub cmdDelLine_Click()
Dim intData As Integer
Dim strData As String
Dim DelRow As Integer

strData = UserInput.Value

If IsNumeric(strData) = True Then
If strData <> 0 Then
intData = CInt(strData)
DelRow = intData + 13
ActiveSheet.Unprotect
Rows(DelRow).EntireRow.Delete
ActiveSheet.Protect
Unload Me

End If
End If
DelRecord.Hide
MsgBox "Invalid Entry, Please Re-Enter"
UserInput = ""
DelRecord.Show

End Sub


 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Private Sub cmdDelLine_Click()
Dim intData As Integer
Dim strData As String
Dim DelRow As Integer

    strData = UserInput.Value
    
    If IsNumeric(strData) = True Then
    
        If strData <> 0 Then
        
            intData = CInt(strData)
            DelRow = intData + 13
            ActiveSheet.Unprotect
            Rows(DelRow).EntireRow.Delete
            ActiveSheet.Protect
            Unload Me
        End If
    Else
    
        MsgBox "Invalid Entry, Please Re-Enter"
    End If
    
    DelRecord.Hide
    UserInput = ""
    DelRecord.Show

End Sub
 

rbi

New Member
Joined
Oct 3, 2009
Messages
7
Thanks xld. This sort of works too. It deletes the line ok but re-presents the form. In other words it seems to be ignoring the "Unload Me" command. Any thoughts?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Of course it does, you explicitly tell it to.

If you don't want it to do that, remove the line that shows it again.
 

rbi

New Member
Joined
Oct 3, 2009
Messages
7
Perfect ! I just moved the End If to right above End Sub and all is well. Thank you much for the help on this bugger. Ron
 

rbi

New Member
Joined
Oct 3, 2009
Messages
7
The code below now works to 99.99% perfection. I only have one thing that I'd like it to do and that is when the form is presented after an invalid entry event, how do I set the focus to the UserInput box? Currently the form re-presents with the focus on the "Delete Line" button.

Private Sub cmdDelLine_Click()
Dim intData As Integer
Dim strData As String
Dim DelRow As Integer

strData = UserInput.Value

If IsNumeric(strData) = True Then

If strData <> 0 Then

intData = CInt(strData)
DelRow = intData + 13
ActiveSheet.Unprotect
Rows(DelRow).EntireRow.Delete
ActiveSheet.Protect
Unload Me
End If
Else
DelRecord.Hide
MsgBox "Invalid Entry, Please Re-Enter"
UserInput = ""
DelRecord.Show
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top