Part 2 : Few Details required of Individual's Name with Range Address in ComboBox

S

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
Hello

Wanted the List of Individuals Name with their Range Address uploaded in combobox

So when the form is initialized all the individual's name with range address is displayed in the combobox
When clicking on each name in combobox i get the respective information in respective textboxes.
in continuation with thread no: 1097415

In combobox it should display like
jerry A1:D10
mike A12:D19
Kate A21:D34
Steve A36:D42

Following is the Structure of worksheet :
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Individual Name:[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 2[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]sfdfdsf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03-04-2015[/TD]
[TD]vchr no : 3[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]rerrt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04-04-2015[/TD]
[TD]Agst vchr no : 2[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]rwewrew[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Individual Name:[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1750[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]gfhfhgfhgf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4[/TD]
[TD][/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]oipoiopo[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]1750[/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD][/TD]
[TD]1400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Individual Name:[/TD]
[TD]Kate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]01-04-2015[/TD]
[TD]vchr no : 5[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD]dfgfgff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]05-04-2015[/TD]
[TD]vchr no : 6[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]fsfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]06-04-2015[/TD]
[TD]Agst vchr no : 12[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD][/TD]
[TD]fdfds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]09-04-2015[/TD]
[TD]Agst vchr no : 5[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD]ggfgfgfg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]10-04-2015[/TD]
[TD]Agst vchr no : 2[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD]fdfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]2100[/TD]
[TD]3800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD]1700[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Individual Name:[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1235[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD]dfdsfds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD]fdfdsf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD]1235[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks
SamD
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
So for this example I have the list in column "H"
With single occurance Textbox names as
TB_Name
TB_TotalPayMade
TB_TotalPayRec
TB_Balance
Then remainder as
TB_Name(1-8)
TB_Description(1-16)
TB_PayMade(1-8)
TB_PayRec(1-8)

In module
Code: 
Option Explicit
Public LastRowNo As Long
Public ListRange As String


Sub PrimeCombo()
LastRowNo = ActiveSheet.Range("H65536").End(xlUp).Row
ListRange = "$H$1:$H$" & LastRowNo
UserForm1.ComboBox1.List = ActiveSheet.Range(ListRange).Value
UserForm1.Show


End Sub

In Combobox1
Code: 
Option Explicit


Private Sub ComboBox1_Change()
Dim StartRow As Long
Dim Lastrow As Long
Dim SelRange As String
Dim SelText As String
Dim XText As String
Dim TransLoop As Long


'Extract address
SelText = UserForm1.ComboBox1.Value
SelRange = Mid(SelText, InStr(SelText, " ") + 1, Len(SelText))
'Extract rows
StartRow = CInt(Mid(SelRange, 2, InStr(1, SelRange, ":") - 2))
Lastrow = CInt(Mid(SelRange, InStr(2, SelRange, ":") + 2, Len(SelRange)))
'Only 8 transactions per customer
If (Lastrow - StartRow >= 4) And (StartRow > 0) And (((Lastrow - StartRow) + 1 - 4) / 2) <= 8 Then
    'Write single lines
    UserForm1.TB_Name = Sheets("Sheet1").Range("B" & StartRow).Value
    UserForm1.TB_TotalPayMade = Sheets("Sheet1").Range("C" & Lastrow - 1).Value
    UserForm1.TB_TotalPayRec = Sheets("Sheet1").Range("D" & Lastrow - 1).Value
    UserForm1.TB_Balance = Sheets("Sheet1").Range("C" & Lastrow).Value
    'Descriptions 1st
    For TransLoop = 1 To (Lastrow - StartRow) + 1 - 4
        UserForm1.Controls("TB_Description" & TransLoop).Value = Sheets("Sheet1").Range("B" & StartRow + TransLoop + 1).Value
    Next TransLoop
    'Remainder of transactions
    For TransLoop = 1 To (((Lastrow - StartRow) + 1 - 4) / 2)
        UserForm1.Controls("TB_TransDate" & TransLoop).Value = Format(Sheets("Sheet1").Range("A" & StartRow + (TransLoop * 2)).Value, "dd/mm/yyyy")
        UserForm1.Controls("TB_PayMade" & TransLoop).Value = Sheets("Sheet1").Range("C" & StartRow + (TransLoop * 2)).Value
        UserForm1.Controls("TB_PayRec" & TransLoop).Value = Sheets("Sheet1").Range("D" & StartRow + (TransLoop * 2)).Value
    Next TransLoop
End If
End Sub
 
Upvote 0
thanks Nemmi69 for the start
So for this example I have the list in column "H"
With single occurance Textbox names as
TB_Name
TB_TotalPayMade
TB_TotalPayRec
TB_Balance
Then remainder as
TB_Name(1-8)
TB_Description(1-16)
TB_PayMade(1-8)
TB_PayRec(1-8)
Click to expand...
what do i have to with above details copy the same in column h or copy what in column h. if you can show in the columnar form will appreciate it
without above clarity i think i am stuck

Also the following code i found in this Forum but changed one variable to suit but shows all the values of column B.
what i want is the individual name and range address in combobox and not all values of column b . it shows all the values of colb with range address
Code: 
Public Sub GetIndividual_Name_Range()

  Dim Ray() As String
  Dim C As Range, LastA As Range, fnd As Range
  Dim rws As Long, k As Long
  Sheet1.Activate
  
  Set LastA = Sheet1.Range("A" & Range("D" & Rows.Count).End(xlUp).Row)

   For Each fnd In Sheet1.Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    rws = 1

If IsEmpty(fnd.Offset(1).Value) And fnd.Address <> LastA.Address Then rws = rws + Sheet1.Range(fnd, LastA).SpecialCells(xlConstants).Areas(1).Rows.Count

    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = fnd.Value & " " & fnd.Resize(rws, 6).Address(0, 0)
  Next fnd
  ComboBox1.List = Ray

End Sub
SamD
 
Last edited:
Upvote 0
In H1 down would be your list
jerry A1:D10
mike A12:D19
Kate A21:D34
Steve A36:D42
 
Upvote 0
Really i've not tried applying any combobox/textbox on worksheet. Always have used on userform. so how could i create the same on worksheet.
 
Last edited:
Upvote 0
The module code will read of the worksheet and generate an address. This is set as the list that your combobox will use on your userform.
 
Upvote 0
How about
Code: 
Private Sub UserForm_Initialize()
   Dim Rng As Range
   
   For Each Rng In Sheet1.Range("B:B").SpecialCells(xlConstants).Areas
      With Me.ComboBox1
         .AddItem Rng(1).Value
         .List(.ListCount - 1, 1) = Rng.CurrentRegion.Address(0, 0)
      End With
   Next Rng
   Me.ComboBox1.ColumnCount = -1
   Me.ComboBox1.ColumnWidths = "50;50"
End Sub
 
Upvote 0
Thanks Fluff . The PERFECT one

Such short and really efficient coding. :)
This is what was required
Thank you so much :beerchug:

SamD
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Few Details required of Individual's Name with Range Address
2
Replies
14
Views
2K
SamDsouza
S
S
  • Question Question
Searching Value in ComboBox as per comboBox Columns
Replies
0
Views
591
SamDsouza
S

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top