Call a Textbox using a variable

Solved
Villette54 Posted messages 300 Registration date   Status Member Last intervention   -  
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   -
Hello,

I have a small issue with VBA. I want to share a variable between two UserForms so I can reuse this variable in a command (I think this is where the problem lies). To summarize, in my first UserForm I have several textboxes intended to hold dates, my second UserForm is a calendar where we choose the date and re-implant it into the first UserForm. So far so good, the problem is that for my current macro to work, I need a calendar UserForm for each textbox intended to hold a date since I specify the name of my textbox in the calendar UserForm. I would therefore like to create a variable so as not to multiply calendar UserForms (I have 10 textboxes that can hold dates).

Here is what I did:

In a module I declared my variable
Public date2 As Date Public CaseDate As String

In my first UserForm I created a button next to each textbox that can hold a date on this model:
Sub Calendar1_Click() CaseDate = Echeance1 'name of my textbox Calendar1.Show vbModeless End Sub

And finally on my calendar UserForm, I choose to re-implant the selected date:
'Traitement.Echeance1.value = date2' this works but I must thus create a calendar UserForm for each textbox name
Traitement.CaseDate.Value = date2 'This does not work (it looks for something named CaseDate on my UserForm directly) but it would allow me to manage everything with the same calendar.
Traitement.Show vbModeless
Unload Me

I’m sorry, I’m probably doing this very badly...
Thanks in advance for your help.

2 answers

pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Hello,

Maybe try:
Traitement.Controls(CaseDate).Value = date2

If CaseDate is a String, that should work...

--
Best regards,
Franck
0
Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
 
Hello Pikaju, Thank you for your help. While trying your solution, I realized another problem (which existed already but I probably hadn’t noticed)... Indeed, before executing this line: Traitement.Controls(CaseDate).Value = date2 VBA returns to my USF "Traitement" and launches Userform_Initialize, which causes problems since it resets to the default values of my USF, so if changes had been made before choosing the date, everything is lost... (and in this case it causes my macro to fail because there is a value it can't retrieve). Is it possible to prevent the launch of this procedure, and thus to see if your solution works? Thanks in advance,
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
When you "switch" from your Treatment UserForm to your calendar, how do you "close" Treatment? UnLoad Traitement or Traitement.Hide Normally Traitement.Hide only hides the userform and thus does not relaunch Initialize. Worth checking though. Otherwise it doesn’t close Treatement and you should use the Modal properties of your userforms...
0
Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
 
I'm lost here, but really completely...

In the beginning I didn't close any USF; I used the modal properties (rather non-modal) so that Calendar1 would "pass over" the processing, without closing anything.
But not knowing all the peculiarities of a non-modal USF, I gave up and simply hid my "Processing" ... And finally, in desperation, I tried to Unload my "Processing".

In all 3 cases, at the moment I run this line
Traitement.Controls(CaseDate).Value = date2

VBA restarts the initialization of processing.

I really don't understand why this procedure re-launches every time...

Regarding the complete USF, I don't know if it will help you. For my part, I don't understand 90% of these procedures because the USF isn't mine and I kept the code as is.
http://cjoint.com/data3/3HgpcBZSioi.htm

PS: My question has nothing to do with the original topic sorry.
Edit: Link error.
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Could you please share the workbook instead, even without data... with the two userforms and their codes...
0
Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
 
I removed a few data items, but I left you two examples and the basic data so the file can be used.

I apologize in advance, because it may burn your eyes... especially the code I created myself, the formulas, or even the layout...

Thanks again for your help.

http://cjoint.com/data3/3Hgpzfp92mU.htm
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
If you are talking about the userform that launches with the small + next to the due date of action 1, it works super well... The date entered in the calendar goes where it needs to without messing anything up............
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Look at your returned file.
I made the first four buttons "+" for you. They work fine.
However, Calendar is the name of a VBA control. So I renamed your buttons to Calendar...
0
Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
 
Hello Pikaju, Indeed, the date insertion works without issues. But if you try to modify the text beforehand (e.g., I change the incident description, I set an end date and then validate → my changes in the description are not taken into account, because the USF was initialized at calendar usage (look step by step)). However, I can't use the file you sent me: as soon as I click on a + it shows me a "Compile Error: Method or data member not found" on calendar1 even though it is there with the correct name (I hate the Excel version we have at work, I often get such unexplained errors while I don't get them at home). Thanks again for your help.
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Hello,

"Compilation Error: Method or data member not found"

My fault, my very big fault....
I named a command button Calendar1 just like the userform... It works fine by renaming the userform Calendar.

On the other hand, I can't reproduce the second bug. For me everything is fine...

The file with a few changes (notably in Module 1)
https://www.cjoint.com/?DHhihUymFko
0
Villette54 Posted messages 300 Registration date   Status Member Last intervention   28
 
Okay for the solution to the first bug... I hadn't even noticed, I really lack practice... For the second bug, I managed to solve it with a new variable to bypass the initialize procedure, so that won't be a problem anymore. Thanks for revising my Module 1 a bit; I indeed had some difficulties creating it (also I hadn't initialized my variable...). I think all the issues mentioned have been resolved... Thanks a lot. May I be so bold as to ask you to take a look at another one of my concerns here? Or should I create a dedicated topic (which might help others)? It mainly concerns input/display in the textboxes (the date format, and ensuring there are dependent dropdown menus). Thanks again.
0