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 -
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.
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
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
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
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...
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...
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.
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
"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
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.
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
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.