permanently storing variables until I change it

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
439
Office Version
  1. 2016
Hey guys,

im trying to store variables so that when I click on a button and run a sub it doesnt forget it the next time I run it. The only time I want it to change is when I run the initial sub that requests the input box variables. I also would lik excel to remember these variables when I close and reopen the workbook. How can this be done?

Option Explicit
Global data_wbk1 As String
Global data_wbk2 As String
Global data_wbk3 As String
Global data_wbk4 As String
Global data_wbk5 As String
Global data_wbk6 As String
Global data_wbk7 As String
Global data_wbk8 As String
Global data_wbk9 As String
Global data_wbk10 As String


Public Sub openfiles()

data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
data_wbk3 = Right(data_wbk2, 5)
data_wbk1 = "FY" & Right(data_wbk3, 2)
Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & data_wbk1 & "\" & data_wbk2 & "\" & "CARS" & "\" & data_wbk3 & " " & "CARS Detail Transaction Lines.xls*")
data_wbk1 = "FY" & Right(data_wbk3, 2)
data_wbk4 = data_wbk1
data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
data_wbk5 = Left(data_wbk2, 2) & " " & "-" & " " & Mid(data_wbk2, 4, 3) & " " & "20" & Right(data_wbk2, 2)
data_wbk6 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")

data_wbk7 = Left(data_wbk3, 3)
data_wbk8 = InputBox("Enter Prior month Name I.E. APRIL:", Default:="APRIL")
data_wbk9 = InputBox("Enter Prior month I.E. 07 - APR 2020", Default:="07 - APR 2020")
data_wbk10 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202004")

THanks!

Jordan
 
That's using a totally different path to your original code.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
That's using a totally different path to your original code.
yes it is. The commented out part is where I define the variables and that is a totally different sheet I want it to open. Just the other path is for a different reconciliation and I only brought in the commented out code so I could remember what I needed to replace the data_wbk2 with.
 
Upvote 0
I would assume that your worksheet name is not that "YourSheetName" so it needs to at least be changed.

I only used it as an example.
 
Upvote 0
With the workbook you were originally opening in your code, how have you recoded that?
 
Upvote 0
I would assume that your worksheet name is not that "YourSheetName" so it needs to at least be changed.

I only used it as an example.
you are correct thanks! I am having one more issue now. I run the following code to place the variables in the variances sheet and it returns a different value than I was expecting.
data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
ThisWorkbook.Worksheets("Variables").Range("A1").Value = data_wbk2
data_wbk3 = Right(data_wbk2, 5)
ThisWorkbook.Worksheets("Variables").Range("A2").Value = data_wbk3
data_wbk1 = "FY" & Right(data_wbk3, 2)
ThisWorkbook.Worksheets("Variables").Range("A3").Value = data_wbk1
Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & data_wbk1 & "\" & data_wbk2 & "\" & "CARS" & "\" & data_wbk3 & " " & "CARS Detail Transaction Lines.xls*")
data_wbk4 = data_wbk1
ThisWorkbook.Worksheets("Variables").Range("A4").Value = data_wbk4
data_wbk5 = Left(data_wbk2, 2) & " " & "-" & " " & Mid(data_wbk2, 4, 3) & " " & "20" & Right(data_wbk2, 2)
ThisWorkbook.Worksheets("Variables").Range("A5").Value = data_wbk5
data_wbk6 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
ThisWorkbook.Worksheets("Variables").Range("A6").Value = data_wbk6
data_wbk7 = Left(data_wbk3, 3)
ThisWorkbook.Worksheets("Variables").Range("A7").Value = data_wbk7
data_wbk8 = InputBox("Enter Prior month Name I.E. APRIL:", Default:="APRIL")
ThisWorkbook.Worksheets("Variables").Range("A8").Value = data_wbk8
data_wbk9 = InputBox("Enter Prior month I.E. 07 - APR 2020", Default:="07 - APR 2020")
ThisWorkbook.Worksheets("Variables").Range("A9").Value = data_wbk9
data_wbk10 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202004")
ThisWorkbook.Worksheets("Variables").Range("A10").Value = data_wbk10

its almost as if excel is taking the value and automatically changing it to a date and thats why it looks different.

ie 08-May20
-may-
turns into 08-may-20
8-May-20​
20-May​
FY20
FY20
8-May-20​
202005​
MAY
APRIL
7-Apr-20​
202004

 
Upvote 0
Yes.
Excel automatically converts them to dates, which change their format.

Try if this help. (Run it before You save anything on those cells.)

VBA Code:
ThisWorkbook.Worksheets("Variables").Range("A1:A10").NumberFormat = "@"
 
Upvote 0
Yes.
Excel automatically converts them to dates, which change their format.

Try if this help. (Run it before You save anything on those cells.)

VBA Code:
ThisWorkbook.Worksheets("Variables").Range("A1:A10").NumberFormat = "@"
awesome THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! worked like a charm. Thank you so much! I am learning so much here over the years. Thanks Tupe for your help! ALso, thanks fluff for your willingness to help you have always helped me over the years. Take care until next time!

Jordan
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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