Hi, is this what you need?
1) this sub will be called when user select something in the ComboBox
2) the value is parsed, separated by "-"
3) active sheet is the PLAN sheet: move it to the first
4) name the rest of sheets by month name
5) also put month name to cell [A1] in corresponding sheet
If full name is needed (i.e. April instead of apr), can add simple
translation routine to do it.
Private Sub ComboBox1_Change()
Dim strQTR As String
strQTR = ComboBox1.Value
ActiveSheet.Move before:=Worksheets(1) ' move PLAN as 1st sheet
Worksheets(2).Name = Split(strQTR, "-")(0) ' 1st month
Worksheets(3).Name = Split(strQTR, "-")(1) ' 2nd month
Worksheets(4).Name = Split(strQTR, "-")(2) ' 3rd month
Worksheets(2).[A1] = Worksheets(2).Name ' cell [A1] has the name
Worksheets(3).[A1] = Worksheets(3).Name
Worksheets(4).[A1] = Worksheets(4).Name
End Sub
--AC
"Joel" wrote:
> MonthSelected = combobox1.Value
>
> For Each sht In Sheets
> If UCase(sht.Name) <> "PLAN" Then
>
> 'test if there is a dash in sht names
> If InStr(MonthSelected, "-") > 0 Then
> Mnth = Left(MonthSelected, _
> InStr(MonthSelected, "-") - 1)
> sht.Name = Mnth
> MonthSelected = Mid(MonthSelected, _
> InStr(MonthSelected, "-") + 1)
> Else
> sht.Name = MonthSelected
> Exit For
> End If
> End If
> Next sht
>
>
> "Vladimir" wrote:
>
> > The workbook has already 4 worksheets: Plan and 3 reports.
> > In the first worksheet (plan) with a toolbox combobox the user will choose 1
> > of 4 options:
> > Jan-feb-march
> > apr-may-june
> > july-aug-sept
> > oct-nov-dec
> >
> > when 1 of 4 options is choosen automatically other 3 worksheets shoud
> > receive respective names: say apr, may, june and in a cell of each worksheet
> > should appear April, May or June correspondingly.
> >
|
Similar Threads
