PDA

View Full Version : btn click userform add row



House
09-18-2007, 10:39 AM
This is my first post, have been using this for years but always found what I need until today.

I'm currently wanting to check a textbox on a userform if a specific region was entered, and if so, add the entire row tothe worksheet. I removed the code for the remainder of the userform to avoid confusion.

Private Sub btnFirmAdd_Click()

Dim strRegion As String
strRegion = txtRegion.Text
With frmAddRow
If txtRegion.Text <> "" Then
Select Case strRegion
Case "Kamloops"
Range(Kamloops).EntireRow.Insert
Case "Kelowna"
Range(Kelowna).EntireRow.Insert
Case "Lower Mainlaind"
Range(Lower_Mainland).EntireRow.Insert
Case "Vancouver Island North"
Range(Vancouver_Island_North).EntireRow.Insert
Case "Vancouver Island South"
Range(Vancouver_Island_South).EntireRow.Insert
End Select
End If
End With

End Sub

I constantly get Runtime Error 424. Any help would be greatly appreciated.

Norie
09-18-2007, 10:58 AM
Try this.


Private Sub btnFirmAdd_Click()

Dim strRegion As String
strRegion = txtRegion.Text
If txtRegion.Text <> "" Then
Range(strRegion).EntireRow.Insert
End If
End With

End Sub

House
09-18-2007, 11:12 AM
Thank you Norie, unfortunately I am still receiving an error of 424. I realize that in vba the forms are treated as classes. Let's simply. I want a singular text box on a userform to check for 5 different city names, then add an additional row which the has the city name inputted into the first row.

Bob Phillips
09-18-2007, 11:15 AM
By way of explanation, your code fails because the range value is a string, such as "KamLoops". Because you are not using quotes, VBA is treating it a s a variable called KamLoops, which will be empty.

Bob Phillips
09-18-2007, 11:18 AM
Thank you Norie, unfortunately I am still receiving an error of 424. I realize that in vba the forms are treated as classes. Let's simply. I want a singular text box on a userform to check for 5 different city names, then add an additional row which the has the city name inputted into the first row.

Perhaps you want



Private Sub btnFirmAdd_Click()

Dim strRegion As String
strRegion = txtRegion.Text
If txtRegion.Text <> "" Then
Rows(Application.Match(strRegion, Columns(1), 0)).Insert
End If
End With

Norie
09-18-2007, 11:19 AM
What's the error message?

Do these named ranges actually exist?

Are you actually trying to use named ranges?

House
09-18-2007, 11:22 AM
i appologize for my poor english, i am french canadien, and not explain myself properly. still error 424

House
09-18-2007, 11:35 AM
Yes...ranges are named "Kamloops", "Kelowna", "Lower Mainland", "Vancouver Island North", "Vancouver Island South"

Bob Phillips
09-18-2007, 12:09 PM
With spaces or underscores?

Norie
09-18-2007, 12:35 PM
You can't have named ranges with spaces in the name.

PS Could you actually tell us the rest of the error message?

Error 424 is just really the error number, there should also be a error description.

Bob Phillips
09-18-2007, 01:13 PM
I know, he gave with spaces, but the code had underscores, presumably wht he had the big Case Select rather than a simple value substitution, and I was trying to get at what he was thinking.

Norie
09-18-2007, 03:24 PM
xld

I see that the OP is using underscores, but are those underscores being input into the textbox?

Bob Phillips
09-19-2007, 12:46 AM
Exactly my point.