VBA button work 3 activity at one click?

[amartakale]

Dear All

Can VBA button work 3 activity at one click.if I click button then 3 things done by code.it is possible? Pls see my sheet.

Regards
Amar

[Paul_Hossler]

I don't know what the 3 things are, since your example only had B1 referenced

I assigned this macro to all the year buttons



Option Explicit

Sub YearToB1()
Application.EnableEvents = False
Me.Range("B1").Value = Me.Shapes(Application.Caller).DrawingObject.Text
Application.EnableEvents =
End Sub

[amartakale]

error show

[amartakale]

If I click VBA Button then 3 activity done by VBA button code at one times
1) my own code already attached to year button which woek perfectly for me (so ignore this one)
2) Show year in B1 cell
3) after criteria match B2 then highlight year column as per B2 field. (if Cell B1=2017 & B2= Sales then should be select O column)

Sir I hope you understand my Question

[Paul_Hossler]

Application.Events=True

[Paul_Hossler]

Sir I hope you understand my Question

No

Your spreadsheet in post #1 had no VBA

[amartakale]

Application.Events=True

now working. Thanks

[amartakale]

Dear Paul Sir

Actually Attached sheet have no vba code. bcos of that code is confidential of office work so not share. but I want with this code 2 more code required where they work at one time one click by button.as per criteria.

Means 3 code attached for 1 VBA button ,is it possible?

[Paul_Hossler]

Without having any insight as to the other macros, this is only a technique



Option Explicit

Sub YearToB1()
Application.EnableEvents = False
Me.Range("B1").Value = Me.Shapes(Application.Caller).DrawingObject.Text

Call OtherMacroOne(Me.Range("B1").Value)
Call OtherMacroTwo(Me.Range("B1").Value)

Application.EnableEvents = True
End Sub

Sub OtherMacroOne(Y As Long)
MsgBox "This is another macro, with Year = " & Y
End Sub

Sub OtherMacroTwo(Y As Long)
MsgBox "This is the second macro, with Year = " & Y
End Sub

[amartakale]

Not Understand Sir what you do.But thanks for you Cooperation to solve this issue.

[amartakale]

Paul Sir
I am Explaining Very Clearly now & Attached excel with one of code.only will attached 2 other vba code for Year button.then will perfectly done my work. Also I attached 2 screenshot in word file how to display required in excel after click button means 3 activity done at one time.
Pls see this sheet

[Paul_Hossler]

How would you filter Col A by year, say 2018? There are many full dates, years, and a mixture?

Usually, the 'Year' column would contain just a Year (say 2018), or possibly a Date (say Oct 9, 2018)

Even the "dates" are not really dates ("9_Oct_18" or "Sep_18")

With all the extra text "2018 / 2017 / 2016 ……." you can't easily filter on just a year or a date range


23091

I suggest that you rethink the format to make it simpler and consistent

You also left a password on the XLSM so I didn't look at your macros

[amartakale]

Dear Paul Sir,

Good morning

I just ask on this forum,it is possible or not as per my criteria? If No, then no issue no problem paul sir.its ok.
Thanks for your cooperation in this issue.

And I attached remove password sample sheet here.Pls see it.

Thanks
Amar Takale23096

[Paul_Hossler]

If you make the format of your worksheet more consistant, it's do-able

The first problem is how to select a year in column A which had not real dates, and has a variety of text formats

[amartakale]

Dear Sir

If I click button then automatically show year in B1 (1 activity) & on this basis Year Highlighted column year with criteria match in B2
(2 activity) and also sort data (3 activity) automatically when I click that Year button.

Ignore G & H column if date confused in different format only consider date (2007 to 2021) as same format. If you have any idea for first 2 date how to implement with 2007 to 2021 then do it,no issue.Actually 9_Oct_18 date is Current date when I update file & Sep_2018 is previous date of last month. Both date change every time & I will change first 2 button name as per.

Whole this activity I want through VBA code in one shot. This is my idea only,If possible then OK otherwise no issue.

Thanks very much

Regards
Amar


23099

[amartakale]

Hi Pual Sir

It can be possible or not?

[Paul_Hossler]

Yes

I only did 3 buttons, the rest are similar




Option Explicit

Private Sub FilterAndSortData(sYear As String, sWeightCol As String)
Dim rData As Range, rFirst As Range, rLast As Range, rWeight As Range

Application.ScreenUpdating = False

With Worksheets("Sort Nifty Stocks Yearwise")
Set rFirst = .Range("A7") ' A7
Set rLast = rFirst.End(xlDown) ' A97
Set rData = Range(rFirst.EntireRow, rLast.EntireRow) ' 7:97
Set rData = Intersect(rData, rFirst.CurrentRegion) ' A7:BP97

Set rWeight = .Range(sWeightCol)

rData.EntireColumn.Hidden = False
If .FilterMode Then .ShowAllData

rData.AutoFilter Field:=1, Criteria1:="=*" & sYear & "*", Operator:=xlAnd
.Range("B2").Value = sYear

Set rWeight = .Range(sWeightCol)

With .Sort
.SortFields.Clear
.SortFields.Add2 Key:=rWeight, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.SetRange rData
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

.Range("A6").Select
End With

Application.ScreenUpdating = True
End Sub



Sub Button_9_Oct_18()
Call FilterAndSortData("9_Oct_18", "G7")
End Sub

Sub Button_Sep_18()
Call FilterAndSortData("Sep_18", "H7")
End Sub

Sub Button_2018()
Call FilterAndSortData("2018", "T7")
End Sub

[amartakale]

Good morning Sir

Thanks for your effort for this tremendous work.but code show error after click.this 3 button.

Thanks
23112

[Paul_Hossler]

Different Excel versions

Change .Add2 to .Add (without the '2')

[amartakale]

Dear Sir

Great,I am very Happy.(I Changed this cell from B2- {.Range("B1").Value = sYear}.

Sir but my last criteria highlighed column as per criteria match in B2 (Data validation cell). not accomplish in this code.If I click button then data sort as per year & year name also show in B1 cell.Thats OK.

Now I want after this 2 criteria done highlighted column as per cell B1 & B2.Below

If in B1 =2017 & B2 =Weig.(%) then "S" cloumn will be highlighted
If in B1 =Sep_18 & B2 =Weig.(%) then "H" cloumn will be highlighted
If in B1 =2015 & B2 =EBIDTA then "BJ" cloumn will be highlighted
If in B1 =2008 & B2 =Sales then "Y" cloumn will be highlighted

Regards
Amar

[amartakale]

This is Another issue. Here I want formula.Pls see sheet.

[Paul_Hossler]

9_Oct_18 and Sep_18 don't have "Sales" or "EBIDTA" columns

Rest is (I think) what you were asking for

2018 button is working, I did not do the others but the process is the same




This is Another issue. Here I want formula. Pls see sheet.

No other sheet

[amartakale]

Dear Paul Sir

Mind Freaking !!! Awesome!!!

Perfect, You are done it………

I am really thankful for its wonderful Code in one shot.

Note: how to direct jump on that year after click button. Now highlighted column is perfectly but I move every time here & there to see highlighted column.

Regards
Amar

[amartakale]

Dear Sir

This is last help required then I am successful in my fully work.

above Same sheet I will extract data in separate sheet year wise stocks extract with formula.All detailed in sheet.Pls see sheet.

Regards
Amar

[amartakale]

23127

[Paul_Hossler]

In col C



=IF(ISERROR(FIND($B$1,$A8)),"NO","YES")


This seems to work and you can integrate the formula into your main spreadsheet

[amartakale]

Dear Paul Sir

Finally done my work by your great Help.Awesome and unbelievable, it was more than expected.
.
Thank you so much, your assistance is greatly appreciated

Regards
Amar