View Full Version : [SOLVED:] Userform for Project closing
djemy1975
09-01-2018, 03:54 AM
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
09-01-2018, 08:12 AM
I have made the outline of this userform but got stuck in sorting data based on a combobox.
Sorting what?
djemy1975
09-01-2018, 10:50 AM
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
09-01-2018, 07:11 PM
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
09-02-2018, 12:53 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.