-1

Would anyone know if it possible or have any ideas on how I could allow a user to update certain parts of a existing Private Sub Code?

For example:

Current: UserForm_Initialize Textbox1 = "Test"
After User Update: UserForm_Initialize TextBox1 = "Hello"

halfer
  • 19,824
  • 17
  • 99
  • 186
new11
  • 55
  • 6
  • 2
    If you want to keep changes for the subsequent run, you can write VBA code programatically from within a VBA modul (like this: https://stackoverflow.com/questions/34837006/excel-vba-add-code-to-sheet-module-programmatically), but you can easily end up in a very complex structure. I'd rather keep changing variables somewhere, e.g. in a .ini file. If you want to keep the changes only for the running session then the userform keeps the values until you unload it. Even then you can keep the values outside of the userform e.g. in a global var. – AcsErno Aug 29 '20 at 08:50
  • 1
    Do you mean making the UserForm to Initialize in order to use the last TextBox1 value (given by the user)? – FaneDuru Aug 29 '20 at 09:29

1 Answers1

0

Supposing that you need the form opening next time displaying the last text used in TextBox1, please use the next approach:

  1. Copy the next declarations on top of the form code (in the declarations area):
Option Explicit

 'It is good to use relevant constants, able to better reflect their meaning:
 Private Const MyApp As String = "MyApplication"
 Private Const Sett As String = "Settings"
 Private Const Txt1 As String = "Txt1Val"
  1. Put this code in the TextBox1 Change event:
Private Sub TextBox1_Change()
  SaveSetting MyApp, Sett, Txt1, TextBox1.Text
End Sub
  1. Copy the next code in the Userform_Initialize event:
  Dim Txt1Val As String
  Txt1Val = GetSetting(MyApp, Sett, Txt1, "No value")
  If Not Txt1Val = "No value" Then Me.TextBox2.Text = Txt1Val

Now, when the user change the TextBox1 text, the new string is memorized in Registry and used to to be load during the next UserForm initialization...

If something unclear, do not hesitate to ask, please.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Savesettings stores data in the system registry; if you move to another computer or operating system, the data will no longer be available. Much better to use a Name from the Workbook or, more simply, a cell. – Zer0Kelvin Aug 29 '20 at 16:08
  • @Zer0Kelvin: Did you see such a request in the question? I did not. Anyhow, the question is short an not clear enough. I asked for clarifications, but no answer. I also mentioned the assumption based on which the code works. And writing - reading the Registry is very fast and does not consume Excel resources. – FaneDuru Aug 29 '20 at 16:14
  • I didn't understand what you mean by "Did you see such a request in the question?" However, the question is conceptually wrong but it seems that it intends to memorize the last value entered in the textbox to present it again at the next opening. In regards to using the registry, it is more efficient than using a name, but (slightly) less efficient than using a cell. What I wanted to point out is that the value stored in the registry would not follow the file if it were moved to another PC or if the file was stored in a network folder . – Zer0Kelvin Aug 29 '20 at 21:19
  • @Zer0Kelvin: Of course, not. But nobody said that this would be necessary. Anyhow, in such cases must be a starting point and it will be when the file is move to another PC... – FaneDuru Aug 29 '20 at 21:23
  • @new11: Didn't the above code solve your problem? If not, can you better explain what is to be done, in order to fulfill your need? – FaneDuru Aug 31 '20 at 11:06