Userform for Project closing

[djemy1975]

Hi friends,

I want to make a userform for closing projects so that I can check the necessary data to decide between closing a project or not.

I have made the outline of this userform but got stuck in sorting data based on a combobox.

I have three sheets: "Basic to Sustain","Specific to sustain","Improve-performance" and a sheet named:"FICHE CLOTURE" to get data in.

-In my form the first combobox ("combobox1") should be filled with the three first sheets.the second combobox("combobox2") should be filled with projects code .the rest are textboxes which should be filled according to the second combobox from the equivalent sheet.

Herewith my excel file to understand my concept.

Thanks in advance for all who shall try to help me.

[Paul_Hossler]

I have made the outline of this userform but got stuck in sorting data based on a combobox.

Sorting what?

[djemy1975]

Sorting what?


sorting means to get the corresponding data from the corresponding sheet and column into textboxes .eg:in "Basic to Sustain" sheet when I chose the sheet in the first combobox ,I get only project codes from this sheet and when I choose a project code ,I get the corresponding project name;cost center;appropriation ...etc in textboxes ...

[Paul_Hossler]

Try this

I'd suggest that you rename the controls just to make it easier e.g. instead of "TextBox19" you could rename it to "tbAlloc2015"





Option Explicit
Dim ws As Worksheet
Dim Proj As String

Private Sub ComboBox1_Change()


Select Case ComboBox1.Value
Case Is = "Basic to Sustain"
ComboBox2.RowSource = Names("probasic").RefersTo
Case Is = "Specific to sustain"
ComboBox2.RowSource = Names("prospecific").RefersTo
Case Is = "Improve-performance"
ComboBox2.RowSource = Names("proimprove").RefersTo
End Select

Set ws = Worksheets(ComboBox1.Value)
End Sub

Private Sub ComboBox2_Change()
Dim ProjRow As Long

Proj = ComboBox2.Value
ProjRow = Application.WorksheetFunction.Match(Proj, ws.Columns(5), 0)
TextBox19.Text = ws.Cells(ProjRow, 7).Value


' rest is left as an exercise to the reader



End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim c00 As String

For Each sh In Sheets
If sh.Visible Then c00 = c00 & "|" & sh.Name
Next
ComboBox1.List = Split(Mid(c00, 2), "|")
End Sub

[djemy1975]

I have made the adjustment you recommended and it is working like a charm.

Thank you so much for your kind help.I will mark this thread as solved.

Try this

I'd suggest that you rename the controls just to make it easier e.g. instead of "TextBox19" you could rename it to "tbAlloc2015"





Option Explicit
Dim ws As Worksheet
Dim Proj As String

Private Sub ComboBox1_Change()


Select Case ComboBox1.Value
Case Is = "Basic to Sustain"
ComboBox2.RowSource = Names("probasic").RefersTo
Case Is = "Specific to sustain"
ComboBox2.RowSource = Names("prospecific").RefersTo
Case Is = "Improve-performance"
ComboBox2.RowSource = Names("proimprove").RefersTo
End Select

Set ws = Worksheets(ComboBox1.Value)
End Sub

Private Sub ComboBox2_Change()
Dim ProjRow As Long

Proj = ComboBox2.Value
ProjRow = Application.WorksheetFunction.Match(Proj, ws.Columns(5), 0)
TextBox19.Text = ws.Cells(ProjRow, 7).Value


' rest is left as an exercise to the reader



End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim c00 As String

For Each sh In Sheets
If sh.Visible Then c00 = c00 & "|" & sh.Name
Next
ComboBox1.List = Split(Mid(c00, 2), "|")
End Sub