Userform question

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Re: Userform question if I may ask please

I think i would like to keep the userform as it is in respect of me adding the input & transfer to worksheet so maybe i will make it wider to allow new text box,combobox,listbox etc.

My worksheet is like so.
Column B is customers name.
Column D is where advert was seen.
Column F is telephone number
Column H is post code
Column J is Area
Column L is Price paid
Column N is last cut
Column P is Miles
Column R is Address
Column T is Next Cut Due

Columns A,C,E,G,I,K,M,O,Q,S Are hidden

I do not know what Userform Multipage is
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Re: Userform question if I may ask please

The userform just allows me to input the information for new customers.
This will be the data that will be shown on the userform when a customers name has been selected
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Re: Userform question if I may ask please

MAIT if you want to start from fresh using the info given in respect of name, address, etc etc no problem.
Might be easier so see what you think.

Thanks.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

Your signature line says:
I have learning difficulties so please be patient if i'm slow on the uptake

I think I'm the same.


So if I understand it your happy with the way you have your UserForm setup now for Taking data from the UserForm to the sheet.

So now what would you like? I'm a little slow.

Are you now asking for a way to load data from the sheet into the Userform?

And can we load this data back into the same Textboxes you already have.

And what would you be doing with this data?

Would you be modifying it and then put it back where it came from?


And are you saying you would like a Combobox with all the customer names shown in the sheet column B
And then when you choose the name you want from the Combobox to load the same textboxes back with the information in that row?


 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

I'm curios in Post 11 you showed 10 items
But in your orginal post you only had 7 Textboxes
1 to 7
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Re: Userform question if I may ask please

As my worksheet gets bigger I think it best to user the same form for selecting a customer and viewing information.
This is all the form is about, selecting a customer later on down the road and being able to see the information assigned to that customer.

Same text boxes are ok but I will add a few more as some I typed into worksheet manually.

Should I see a spelling error when viewing info on form maybe best if I could edit it and then being able to save it to the worksheet etc.

Yes to combobox and names in column B

Yes to last question also please
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

Edit
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

Try this:

Put these two codes in your UserForm

This script will load all the Names in Column B into a Combobox

You will need a ComboBox in your Userform named ComboBox1
This is the default name
Code:
Private Sub UserForm_Initialize()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row - 1
ComboBox1.List = Cells(2, 2).Resize(lastrow).Value
End Sub

Now put this script in your ComboBox

When you click on a name it will load all the data for that name into your 7 Textboxes

Code:
Private Sub ComboBox1_Change()
'Modified  5/18/2019  5:21:55 PM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
Dim i As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
For i = 1 To 7
    Controls("TextBox" & i).Value = Cells(SearchRange.Row, i * 2).Value
Next
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

I'm still curios about question in post 15

But assuming you wrote the script you posted I think you will be able to modify my script if needed
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Re: Userform question if I may ask please

Tomorrow I will try the above.

Some items were added manually to the worksheet.
These were not on the list when I started but as the list has become bigger I thought to now do something.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,017
Messages
5,466,057
Members
406,462
Latest member
I10V3xl

This Week's Hot Topics

Top