Data Input on Workbook Open

[default_name]

Hey guys,

I am trying to implement an input box that appears when the user first opens the workbook.
Here is how I would like it to function:

1. User opens the workbook
2. A prompt appears, which asks the user to enter a balance (which is stored as a variable called 'balance')
   • If the user just hits enter or Cancel (without inputting a new balance), then the box disappears, and the value in cell B1 remains unchanged (it remains at whatever value it was already set to prior to opening the workbook)
   • If the user enters a value and hits OK or enter, the input box disappears, and the value of cell B1 is updated to reflect the new 'balance'

I tried to get it started using some input box code I found online, but I am having issues with even the initial setup (not to mention the Cancel/no data inputted function).
Here is what I currently have started.

Private Sub Workbook_Open()
Dim balance As Variant
Do
    balance = InputBox("What is your current balance?")
    If IsNumeric(balance) Then Exit Do
    Range("B1").Value = account
Loop
End Sub

Thanks in advance

 

[Fluff]

What is the name of the sheet that holds the balance?

 

[default_name]

What is the name of the sheet that holds the balance?

It is called 2020

 

[Fluff]

Ok, how about

Private Sub Workbook_Open()
   Dim balance As Variant
   balance = InputBox("What is your current balance?")
   If balance = "" Then Exit Sub
   Sheets("2020").Range("B1").Value = balance
End Sub

 

[default_name]

Ok, how about

Private Sub Workbook_Open()
   Dim balance As Variant
   balance = InputBox("What is your current balance?")
   If balance = "" Then Exit Sub
   Sheets("2020").Range("B1").Value = balance
End Sub

Works perfectly! Thanks for all your help Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[default_name]

Real quick follow-up question to the provided solution.
Hopefully this is ok to ask...if not I can just make a brand new thread.

I am trying to incorporate a Protect Sheet function with the provided code, in the event that "" (no new data) is inputted.
I incorporated a Protect Sheet function, that seems to work just fine, in the event that any new data is inputted, but adding the same Protections (even in the event of a "") seems to be a challenge.

Private Sub Workbook_Open()
Dim balance As Variant
balance = InputBox("What is your current balance?")
If balance = "" Then
                Sheets("2020").Select
                ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _False
           Exit Sub
Sheets("2020").Range("B1").Value = balance
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _False
Sheets("2020").Select
End Sub

 

[EXCEL MAX]

Hello Default Name,
you got some syntax error. Try with some changes:

Private Sub Workbook_Open()
Dim balance As Variant
balance = InputBox("What is your current balance?")
If balance = "" Then
                Sheets("2020").Select
                ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
                
Exit Sub
End If
Sheets("2020").Range("B1").Value = balance
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
Sheets("2020").Select
End Sub

 

[default_name]

Aha I see it!
Thanks Excel Max!