ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Input box question (https://www.excelbanter.com/excel-programming/412969-input-box-question.html)

Ewing25
Input box question
 
I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

Dave Peterson
Input box question
 
Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson

Ewing25
Input box question
 
I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson

Dave Peterson
Input box question
 
I don't see anything like:
wks.Range("X" & lrA + 1) = myStr

But I don't see anything where you put the value of the listbox back into the
worksheet, either.


Ewing25 wrote:

I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson


--

Dave Peterson

Ewing25
Input box question
 
Ah i just replaced

myStr = Me.TextBox5.Value

with

wks.Range("D" & lrA + 1) = TextBox5.Value

Thanks!

"Dave Peterson" wrote:

I don't see anything like:
wks.Range("X" & lrA + 1) = myStr

But I don't see anything where you put the value of the listbox back into the
worksheet, either.


Ewing25 wrote:

I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Input box question
 
And if that textbox is empty, but the listbox has an item selected?????

Ewing25 wrote:

Ah i just replaced

myStr = Me.TextBox5.Value

with

wks.Range("D" & lrA + 1) = TextBox5.Value

Thanks!

"Dave Peterson" wrote:

I don't see anything like:
wks.Range("X" & lrA + 1) = myStr

But I don't see anything where you put the value of the listbox back into the
worksheet, either.


Ewing25 wrote:

I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Ewing25
Input box question
 
Then it shows the listbox item. Which is what i was going for.

"Dave Peterson" wrote:

And if that textbox is empty, but the listbox has an item selected?????

Ewing25 wrote:

Ah i just replaced

myStr = Me.TextBox5.Value

with

wks.Range("D" & lrA + 1) = TextBox5.Value

Thanks!

"Dave Peterson" wrote:

I don't see anything like:
wks.Range("X" & lrA + 1) = myStr

But I don't see anything where you put the value of the listbox back into the
worksheet, either.


Ewing25 wrote:

I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave Peterson
Input box question
 
I don't see how the code you've posted does that, but maybe you got it working
and didn't bother with the post.

If that's the case, good.

If that's not the case, you'll want to post your current code.

Ewing25 wrote:

Then it shows the listbox item. Which is what i was going for.

"Dave Peterson" wrote:

And if that textbox is empty, but the listbox has an item selected?????

Ewing25 wrote:

Ah i just replaced

myStr = Me.TextBox5.Value

with

wks.Range("D" & lrA + 1) = TextBox5.Value

Thanks!

"Dave Peterson" wrote:

I don't see anything like:
wks.Range("X" & lrA + 1) = myStr

But I don't see anything where you put the value of the listbox back into the
worksheet, either.


Ewing25 wrote:

I added the code and changed the textboxs to textbox5 and it still only
return the values in the Listbox.

The code i added:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

Dim myStr As String

If Me.TextBox5.Value < "" Then
myStr = Me.TextBox5.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

"Dave Peterson" wrote:

Dim myStr As String

If Me.TextBox1.Value < "" Then
myStr = Me.TextBox1.Value
Else
If Me.ListBox1.ListIndex < 0 Then
myStr = "None selected"
Else
myStr = Me.ListBox1.Value
End If
End If

MsgBox myStr

Ewing25 wrote:

I have a Userform that has an input box and a listbox.

When the User brings up the userform they have an option of picking
something from the listbox or typing in a new value in the inputbox.

What i want to do is if there is a value in the inputbox then it disregards
anything selected in the input box.

Heres my code so far:

Private Sub CommandButton1_Click()
Set wks = Worksheets("Expense Non Amex")
Set wks1 = Worksheets("Expense Amex")

lrA = wks.Cells(Rows.Count, 1).End(xlUp).Row
lrB = wks.Cells(Rows.Count, 2).End(xlUp).Row
lrC = wks.Cells(Rows.Count, 3).End(xlUp).Row
lrE = wks.Cells(Rows.Count, 4).End(xlUp).Row
lrD = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("A" & lrA + 1) = TextBox1.Text
wks.Range("B" & lrA + 1) = TextBox2.Text
wks.Range("C" & lrA + 1) = TextBox3.Text
wks.Range("E" & lrA + 1) = TextBox4.Text
wks.Range("D" & lrA + 1) = ListBox1.Text

If wks.Range("D" & lrA + 1) = "" Then
wks.Range("D" & lrA + 1) = TextBox5.Text

End If

TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 01:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com