Is it possible to make a button that changes the formula?

W

Want2BExcel

Board Regular
Joined
Nov 24, 2021
Messages
112
Office Version
  1. 2016
Platform
  1. Windows
Is it possible to make a button that changes the formula?

Right now the formula is "pointing" to the expected.expenses. but when a month is over I import all the actual expenses in a new sheet. Now I, of course, know what I have to do to change the formula to point to the new sheet. But if I gave this budget to friends, they don't know what to do. So if there was a button, they just could click on and then a drop-down menu opens to choose witch month he/she would like to apply the "new" formula...voila :)

So from this:
Original in danish: =SUM.HVIS(ANSLÅET.UDGIFTER[Tekst];[@[Postering tekst]];ANSLÅET.UDGIFTER[Januar])
In english: =SUMIF(EXPECTED.EXPENSES[Text];[@[Postering text]];EXPECTED.EXPENSES[January])

The user pushes the button and a dropdown menu opens with all 12 months to choose from. Pick January and the formulas in all rows in that whole column (January) changes to...

To this:
Original in danish: =SUM.HVIS(BUDGET_JAN_22[Tekst];[@[Postering tekst]];BUDGET_JAN_22[Beløb])
In english: =SUMIF(BUDGET_JAN_22[Text];[@[Postering text]];BUDGET_JAN_22[Amount])

It would be amazing!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Danish/English
Just for fun, i added a little bit to much ;)
With the commandbutton, you swap between the danish and the english language in the 2nd listobject.
Without interference, the formulas change in the 1st listobject.

Then, in Cell C4, i change the field, just another month.
Like this you can change every formula in a listobject.
 

Attachments

  • Schermafbeelding 2022-01-22 191011.png
    Schermafbeelding 2022-01-22 191011.png
    20.6 KB · Views: 7
Upvote 0
@BSALV you have already been told not to simply post links to a workbook, but to post all the code. Please do so.
 
Upvote 0
BSALV said:
Danish/English
Just for fun, i added a little bit to much ;)
With the commandbutton, you swap between the danish and the english language in the 2nd listobject.
Without interference, the formulas change in the 1st listobject.

Then, in Cell C4, i change the field, just another month.
Like this you can change every formula in a listobject.
Click to expand...
I don't get it...!? it's a png image and I can't see where or how I could solve my problem from that...sorry
 
Upvote 0
my XL2BB>Mini Sheet was grey, not selectable a few hours ago.
I don't know why, but it's okay now.
There are 2 listobjects in that page, as you can see.

VBA Code: 
Sub ChangeLanguage()
     Dim bDanish

     With ActiveSheet.ListObjects(2)
          bDanish = (.Name = "ANSLÅET.UDGIFTER")                'actual name is danish
          MsgBox "2nd listbox is " & vbLf & .DataBodyRange.Address & vbLf & .Name, , "now " & UCase(IIf(bDanish, "Danish spoken", "English spoken"))     'Address and name 2nd listobject

          If bDanish Then
               .Name = "Budget_JAN_22"                          'english name
               .HeaderRowRange.Cells(1).Resize(, 2).Value = Array("Text", "Amount")     '1st 2 columns english names
               a = [transpose(text(row(A1:A12)*28,"[$-0809]mmmm"))]     'all the months in english
               .HeaderRowRange.Cells(1, 3).Resize(, 12).Value = a     'row 3-15=english months
          Else
               .Name = "ANSLÅET.UDGIFTER"                       'danish name
               .HeaderRowRange.Cells(1).Resize(, 2).Value = Array("Tekst", "Beløb")
               a = [transpose(text(row(A1:A12)*28,"[$-0406]mmmm"))]     'danish months
               .HeaderRowRange.Cells(1, 3).Resize(, 12).Value = a
          End If
     End With

     r = WorksheetFunction.RandBetween(1, 12)                   ' random 1-12 = random month
     s = Range("C4").FormulaR1C1                                'actual formula in C4
     i = InStrRev(s, "[")                                       'position of the last "["
     s = Left(s, i) & a(r) & Right(s, 2)                        'change actual field in formula with another month
     MsgBox "old & new formula & " & vbLf & vbLf & Range("C4").FormulaR1C1 & vbLf & s                 'old and new formula
     With Range("C4")
     .FormulaR1C1 = s                                'give new formula to C4
     .Offset(-2).Value = a(r)
End With

     'Range("A1").FormulaR1C1 = "'=SUM.HVIS(ANSLÅET.UDGIFTER[Tekst];[@[Postering tekst]];ANSLÅET.UDGIFTER[Januar])"
     'Range("A2").FormulaR1C1 = "=SUMIF(EXPECTED.EXPENSES[Text],[@[Postering text]],EXPECTED.EXPENSES[January])"
     'Range("A3").FormulaR1C1 = "'=SUM.HVIS(BUDGET_JAN_22[Tekst];[@[Postering tekst]];BUDGET_JAN_22[Beløb])"
     'Range("A4").FormulaR1C1 = "'=SUMIF(BUDGET_JAN_22[Text];[@[Postering text]];BUDGET_JAN_22[Amount])"
    
End Sub

danish.xlsb
ABCDEFGHIJKLMNOPQRST
1TBL_1ANSLÅET.UDGIFTER/EXPECTED.EXPENSESjanuari
2June
3tekstpostering tekstmonthamountTextAmountJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
4aa311ddd50202224262830323436384042
5bb02a20212325272931333537394143
6
7
8
Blad1
Cell Formulas
RangeFormula
K1K1=TEXT(ROW(),"mmmm")
H4:S5H4=ROW()+COLUMN()*2
C4:C5C4=SUMIF(Budget_JAN_22[Text],[@tekst],Budget_JAN_22[June])
 
Upvote 0
BSALV said:
my XL2BB>Mini Sheet was grey, not selectable a few hours ago.
I don't know why, but it's okay now.
There are 2 listobjects in that page, as you can see.

VBA Code: 
Sub ChangeLanguage()
     Dim bDanish

     With ActiveSheet.ListObjects(2)
          bDanish = (.Name = "ANSLÅET.UDGIFTER")                'actual name is danish
          MsgBox "2nd listbox is " & vbLf & .DataBodyRange.Address & vbLf & .Name, , "now " & UCase(IIf(bDanish, "Danish spoken", "English spoken"))     'Address and name 2nd listobject

          If bDanish Then
               .Name = "Budget_JAN_22"                          'english name
               .HeaderRowRange.Cells(1).Resize(, 2).Value = Array("Text", "Amount")     '1st 2 columns english names
               a = [transpose(text(row(A1:A12)*28,"[$-0809]mmmm"))]     'all the months in english
               .HeaderRowRange.Cells(1, 3).Resize(, 12).Value = a     'row 3-15=english months
          Else
               .Name = "ANSLÅET.UDGIFTER"                       'danish name
               .HeaderRowRange.Cells(1).Resize(, 2).Value = Array("Tekst", "Beløb")
               a = [transpose(text(row(A1:A12)*28,"[$-0406]mmmm"))]     'danish months
               .HeaderRowRange.Cells(1, 3).Resize(, 12).Value = a
          End If
     End With

     r = WorksheetFunction.RandBetween(1, 12)                   ' random 1-12 = random month
     s = Range("C4").FormulaR1C1                                'actual formula in C4
     i = InStrRev(s, "[")                                       'position of the last "["
     s = Left(s, i) & a(r) & Right(s, 2)                        'change actual field in formula with another month
     MsgBox "old & new formula & " & vbLf & vbLf & Range("C4").FormulaR1C1 & vbLf & s                 'old and new formula
     With Range("C4")
     .FormulaR1C1 = s                                'give new formula to C4
     .Offset(-2).Value = a(r)
End With

     'Range("A1").FormulaR1C1 = "'=SUM.HVIS(ANSLÅET.UDGIFTER[Tekst];[@[Postering tekst]];ANSLÅET.UDGIFTER[Januar])"
     'Range("A2").FormulaR1C1 = "=SUMIF(EXPECTED.EXPENSES[Text],[@[Postering text]],EXPECTED.EXPENSES[January])"
     'Range("A3").FormulaR1C1 = "'=SUM.HVIS(BUDGET_JAN_22[Tekst];[@[Postering tekst]];BUDGET_JAN_22[Beløb])"
     'Range("A4").FormulaR1C1 = "'=SUMIF(BUDGET_JAN_22[Text];[@[Postering text]];BUDGET_JAN_22[Amount])"
   
End Sub

danish.xlsb
ABCDEFGHIJKLMNOPQRST
1TBL_1ANSLÅET.UDGIFTER/EXPECTED.EXPENSESjanuari
2June
3tekstpostering tekstmonthamountTextAmountJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
4aa311ddd50202224262830323436384042
5bb02a20212325272931333537394143
6
7
8
Blad1
Cell Formulas
RangeFormula
K1K1=TEXT(ROW(),"mmmm")
H4:S5H4=ROW()+COLUMN()*2
C4:C5C4=SUMIF(Budget_JAN_22[Text],[@tekst],Budget_JAN_22[June])
Click to expand...
Hi BSALV,

I'm TOTALLY new to this part of excel, so I can't "read" VBA...but I want to learn (will watch some YouTube later). But for now, I don't understand a thing.
I finally figured out how to use XL2BB (i think a least), so I will make 3 post of the 3 sheets involved, because I can't understand yours and dont know how to set your VBA up so it matches my workbook. I would REALLY appriciate if you could explain a little about what I should do then... THX!!! :)
 
Upvote 0
the Budgetkonto 2022 sheet
2022 - PrivatBudget ver.2.2.02 - SKABELON - Kopi.xlsx
ABCDEFGHIJKLMNOP
1BeskrivelseBetaling metodePostering tekstJanuarFebruarMartsAprilMajJuniJuliAugustSeptemberOktoberNovemberDecemberIalt
2Bolig
3HuslejeBetalings ServiceUDLEJER---5.700,00---5.700,00---5.700,00---5.700,00-22.800,00
4AffaldBetalings ServiceKOMMUNE--4.500,00------4.500,00-----9.000,00
5-------------
Budgetkonto 2022
Cell Formulas
RangeFormula
D3:D5D3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Januar])
E3:E5E3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Februar])
F3:F5F3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Marts])
G3:G5G3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[April])
H3:H5H3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Maj])
I3:I5I3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Juni])
J3:J5J3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Juli])
K3:K5K3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[August])
L3:L5L3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[September])
M3:M5M3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Oktober])
N3:N5N3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[November])
O3:O5O3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[December])
P3:P5P3=SUM(D3+E3+F3+G3+H3+I3+J3+K3+L3+M3+N3+O3)
 
Last edited:
Upvote 0
the Anslåede.udgifter sheet
2022 - PrivatBudget ver.2.2.02 - SKABELON - Kopi.xlsx
ABCDEFGHIJKLM
1TekstJanuarFebruarMartsAprilMajJuniJuliAugustSeptemberOktoberNovemberDecember
2UDLEJER-5700,00-5700,00-5700,00-5700,00
3KOMMUNE-4500,00-4500,00
Anslåede.udgifter
 
Last edited:
Upvote 0
the Jan '22 sheet
2022 - PrivatBudget ver.2.2.02 - SKABELON - Kopi.xlsx
ABCD
1DatoTekstBeløbSaldo
203-01-22UDLEJER-2000,0028547,25
303-01-22KOMMUNE-500,0027735,31
Jan '22
 
Last edited:
Upvote 0
with your screenshots (except, the sheetname is "Anslåede.udgifter_" to make a difference with the table "Anslåede.udgifter", otherwise VBA gives an error)
VBA Code: 
Sub NewMonth()
     Dim Answ, FL, ShName, sMon, cMyKol As Range, LO_Month As ListObject

     a = [transpose(row(A1:A12) &". " & text(row(A1:A12)*28,"[$-0406]mmmm"))]     'danish monthnames + index
     Answ1 = Application.InputBox("0. Stop, nothing to change" & vbLf & vbLf & Join(a, vbLf), UCase("What month do you want to change"), 0, 100, 100, , , 1)
     If Answ1 = 0 Then Exit Sub                                 '=stop

     Select Case Answ1
          Case 1 To 12
               smonth = Split(a(Answ1))(1)
               sMon = Left(smonth, 3)               'first 3 letters of choosen month
               For i = 1 To Worksheets.Count
                    If StrComp(Left(Worksheets(i).Name, 3), sMon, vbTextCompare) = 0 Then s = s & vbLf & i & ". " & Worksheets(i).Name     'string with all sheets starting with sMon
               Next
               If Len(s) = 0 Then MsgBox "no sheets starting with " & sMon & " found" & vbLf & "sorry", vbInformation: Exit Sub     'no sheets found = end of story
               Answ = Application.InputBox("0. Stop, nothing to change" & vbLf & vbLf & s, UCase("What sheet number do you want ?"), 0, 100, 100, , , 1)     'choice between 1 or several options
               If Answ = 0 Then Exit Sub                        '0=stop
               FL = Filter(Split(Mid(s, 2), vbLf), Answ & ". ", 1, vbTextCompare)     'filter selection of sheets with your answer; normally 1 option left
               If UBound(FL) <> 0 Then MsgBox "fatal error": Exit Sub     'normally only 1 sheet left, so why ???
               ShName = Trim(Mid(Replace(FL(0), " ", WorksheetFunction.Rept(" ", 100), 1, 1, vbTextCompare), 100))     'get rid of the indexnumber
               On Error Resume Next
               Set LO_Month = Sheets(ShName).Range("A1").ListObject     ' the wanted listobjects contains cell A1 of that sheet
               If LO_Month Is Nothing Then MsgBox "no listobject in A1 of " & ShName, vbCritical: Exit Sub     'listobject of that month not found

               Set cMyKol = Sheets("BudgetKonto 22").ListObjects(1).ListColumns(smonth).DataBodyRange
               If cMyKol Is Nothing Then MsgBox "your month " & smonth & " not found", vbCritical: Exit Sub
               On Error GoTo 0
            
               MsgBox cMyKol.Address
               cMyKol.FormulaR1C1 = "=SUMIF(" & LO_Month.Name & "[Tekst],[@[Postering tekst]], " & LO_Month.Name & "[Beløb])"

          Case Else: MsgBox "only 12 months in a year", vbInformation
     End Select

End Sub
Want2BeExcel.xlsm
ABCDEF
1BeskrivelseBetaling metodePostering tekstJanuarFebruarMarts
2Bolig 
3HuslejeBetalings ServiceUDLEJER-2.000,00 -5.700,00
4AffaldBetalings ServiceKOMMUNE-500,00-4.500,00 
5   
6
BudgetKonto 22
Cell Formulas
RangeFormula
D2:D5D2=SUMIF(Tabel3[Tekst],[@[Postering tekst]], Tabel3[Beløb])
E3:E5E3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Februar])
F3:F5F3=SUMIF(ANSLÅET.UDGIFTER[Tekst],[@[Postering tekst]],ANSLÅET.UDGIFTER[Marts])


Want2BeExcel.xlsm
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

W
  • Question
Using VBA to get data from a random .csv and then create, replace and rename the new sheet
Replies
6
Views
1K
Want2BExcel
W
W
  • Solved
How to use text from a cell in a formula, so if I change the text, the formula changes to?
2
Replies
12
Views
1K
Fluff
Fluff
E
  • Solved
Formula referencing a text value of a different cell to complete formula? Possible?
Replies
5
Views
525
essential
E
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
452
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A

Forum statistics

Threads
1,215,467
Messages
6,124,984
Members
449,201
Latest member
Lunzwe73

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top