View Full Version : VBA button work 3 activity at one click?
amartakale
10-22-2018, 05:10 AM
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
10-22-2018, 07:05 AM
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
10-23-2018, 05:05 AM
error show
amartakale
10-23-2018, 05:13 AM
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
10-23-2018, 06:07 AM
Application.Events=True
Paul_Hossler
10-23-2018, 06:09 AM
Sir I hope you understand my Question
No
Your spreadsheet in post #1 had no VBA
amartakale
10-25-2018, 05:35 AM
Application.Events=True
now working. Thanks
amartakale
10-25-2018, 05:39 AM
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
10-25-2018, 07:15 AM
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
10-26-2018, 04:36 AM
Not Understand Sir what you do.But thanks for you Cooperation to solve this issue.
amartakale
10-26-2018, 04:47 AM
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
10-26-2018, 07:06 AM
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
10-26-2018, 09:53 PM
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
10-27-2018, 05:40 AM
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
10-28-2018, 09:57 PM
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
10-31-2018, 05:39 AM
Hi Pual Sir
It can be possible or not?
Paul_Hossler
10-31-2018, 08:25 AM
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
10-31-2018, 10:42 PM
Good morning Sir
Thanks for your effort for this tremendous work.but code show error after click.this 3 button.
Thanks
23112
Paul_Hossler
11-01-2018, 06:46 AM
Different Excel versions
Change .Add2 to .Add (without the '2')
amartakale
11-02-2018, 05:11 AM
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
11-02-2018, 05:46 AM
This is Another issue. Here I want formula.Pls see sheet.
Paul_Hossler
11-02-2018, 06:24 AM
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
11-03-2018, 01:35 AM
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
11-03-2018, 01:51 AM
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
11-03-2018, 02:08 AM
23127
Paul_Hossler
11-03-2018, 12:17 PM
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
11-05-2018, 05:21 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.