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

[lukeshuttlewood]

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

 

[patrickmuldoon99]

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

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:

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

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

Cheers, Patrick

 

[lukeshuttlewood]

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]

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:

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]

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.

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]

Good idea patrick. Thankyou.

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