Problem with VBA form..........

lukeshuttlewood

Board Regular
Joined
Jul 27, 2004
Messages
90
Hi,

I have a form with a text box on it. At present I would like the default text for this text box to be saved as the number 4, (which is simple enough in design mode).

However, I would like users to be able to change this default to another default number ( eg 10) and be able save the new number as default while the form is 'active'.

What code is needed to save the new default number so that the form text box will always disply the new number??

(hope that makes sense!)

Many thanks
Luke
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
You can either have the form control reference a public variable, and then allow that to be written or read to and from to keep the value.

Alternatively you could write whatever is in the text box to a cell, and then have the textbox always look at that cell for its default value.

For example

Code:
sheets("Sheet1").cells(1,1) = me.mytextbox.text

So that will put whatever is in your textbox - called 'mytextbox' here, into cell A1 on Sheet1

You can then make the control look for its value there on a form_initialize event:

Code:
me.mytextbox.text = sheets("Sheet1").cells(1,1)

Hope this helps, post back if i'm not clear!

Cheers, Patrick
 

lukeshuttlewood

Board Regular
Joined
Jul 27, 2004
Messages
90
Thanks Patrick,

Im trying to stay clear of using the SS itself as a means to store info for the form. I know I could use the veryhidden function on the sheets containing this info and protect them via VB. Its just that the people where i work have an amazing knack of f***ing SS's up!

There must be a way to directly save values to a form. (I'll keep looking until i do)

Cheers
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Well the problem with forms is that any variables etc will be lost as soon as it is unloaded. You need somewhere to store this data where it can be called back when the form is loaded again.

You could always write to a temporary text file?

Heres some code I have used to do something like this before:

Code:
Sub LogAppName(AppName As String)
Const LogFileName As String = "C:\RangePlanning.temp"
Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName For Output As #FileNum ' (re)creates the file if it doesn't exist
    Print #FileNum, AppName
    Close #FileNum ' close the file
End Sub

Function RangeWorkFile() As String
Const RangeFileName As String = "C:\RangePlanning.temp"
Dim FileNum As Integer, tLine As String
    FileNum = FreeFile
    Open RangeFileName For Input Access Read Shared As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, tLine
    Loop
    Close #FileNum
    If tLine = "" Then
        LogAppName (ActiveWorkbook.Name)
        RangeWorkFile
    End If
    RangeWorkFile = tLine
End Function

The context which I use the above code is with the following logic

1) The user can theoretically rename the workbook (a range planning tool) - as such any code that moves workbooks (quite a lot) is going to need to know what the name of the workbook is. As this could change at any point, there is an event at before save and on close that calls LogAppName. This function will write the activeworkbooks name to a text file called C:\rangeplanning.temp

2) Then, whenever I use RangeWorkFile in my code, it calls the second function above, and returns the first line in the text file - which is always the name of the range planning workbook.

You could apply this logic, as you will have hard-stored your input default, without actually storing it anywhere in the spreadsheet
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good morning lukeshuttlewood

Is this file to be opened on the same machibe every time? If so you could use the registry to save the value to.

Code:
GetSetting("My Value", "Whatever", "Anything", 4)
SaveSetting "MyValue", "Whatever", "Anything", 4

Other than that it's going to be Patrick's suggestion, or you could save the value to another file on the network. A form can't save a value.

HTH

DominicB
 

lukeshuttlewood

Board Regular
Joined
Jul 27, 2004
Messages
90
Good idea patrick. Thankyou.

Dom, This will be used on many computers but I never knew the registry could be accessed. Definitely useful.
 

Forum statistics

Threads
1,136,268
Messages
5,674,732
Members
419,523
Latest member
Urnovio

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
Top