View original (French)

Empty the ComboBox textboxes.

Ofeu Posted messages 26 Status Member -  
 archer -
Hello,

I am creating a form but I am encountering some issues...

In fact, I still have some obstacles that I have tried to apply to the commands, but I'm still debugging.

Here is the file:
https://www.cjoint.com/c/HHyttDOtEsh

1. I would like in my Excel file (sheet "Model"), when I click on "GO", to open the form and automatically clear the cells (D17:G20) and (G11:H13). I have my command to open the form with the "GO" button, but with different attempts I have not managed to clear these cells from the "Model" sheet.

2. In my form, I added a "REFRESH" button. With this button, I managed to clear the cells (D17:G20) and (G11:H13) of the "Model" sheet. But I would also like to clear the comboboxes (1 and 2), as well as the TextBox (6,7,8, and 9). But with the commands I have tried to implement, it does not work...

3. I don't know if it's possible, but could I make the following commands appear only when I have filled in Combobox1 and Combobox2 (linked to TextBox18).
-Labels (4 to 12)
-TextBox (2 to 17)
-CommandButton (3,4, and 5)

Thank you for your help!

Lucas

5 answers

Answer 1 / 5
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Hello,

I cannot modify your file on Windows, insufficient memory problem even though 1/4 of the RAM is occupied !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I will check on MAC, but I've already had issues opening a file with UF after modifying it on Windows.
So ......
Can you make it available again?
0
Ofeu Posted messages 26 Status Member
 
Hello,

Here is a new version of the file.
https://www.cjoint.com/c/HHzhURRdmNh

Thank you in advance!!!
Lucas
0
Answer 2 / 5
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Re,

Still not okay
0
Answer 3 / 5
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Re,
looking for a command to clear the comboboxes
It's .Clear for each combo......
There is no TextBox18!!!!!!!!!!!!!!!! if I found it
Sheet protection is useless if no cells are locked............
0
Ofeu Posted messages 26 Status Member
 
Hello again,

Thank you, I had also tried these commands:

for CommandButton1 (in my Excel sheet):

'to open the form, and it works well

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True

Next

'to clear the cells in the Excel sheet

Sheets("Model").Range("D17:G20").ClearContents
Sheets("Model").Range("G11:H13").ClearContents

'but this command does not work, and I have already tried other commands and nothing

End Sub


Then, when the form is open, I have CommandButton6

Private Sub CommandButton6_Click()

'to clear the cells in the Excel sheet, the same action as for CommandButton1 which is present in the Excel sheet and not in the form. This command works well, the cells are emptied as soon as I click CommandButton6.

Sheets("Model").Range("D17:G20").ClearContents
Sheets("Model").Range("G11:H13").ClearContents

'I also wanted with CommandButton6 to clear ComboBoxes and TextBoxes, below is my code. However, this does not work.

ComboBox1.Clear
ComboBox2.Clear
TextBox1.Value = ""

End Sub


Thank you for your availability!
Best regards
Lucas
0
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello,

To post your code, see this procedure.
https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code

You need to choose the first option basic

There you go

See you later, Le Pivert
0
Answer 4 / 5
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello,

Like this: 

Private Sub CommandButton6_Click() Reset End Sub Private Sub Reset() Sheets("Model").Range("D17:G20").ClearContents Sheets("Model").Range("G11:H13").ClearContents ComboBox1.Clear ComboBox2.Clear TextBox6.Text = "" TextBox7.Text = "" TextBox8.Text = "" TextBox9.Text = "" End Sub Private Sub UserForm_Initialize() Reset ComboBox1.List = Application.Transpose(Range("MFA")) End Sub


--
@+ The Woodpecker
0
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Hello cs_LePivert,
You were able to modify the Ofeu file, while I couldn't, as seen in the image above.
Could you please share the file you modified?
I would really like to know why things are going wrong with the original file on my end.
On a MAC, there are no issues...
0
Ofeu Posted messages 26 Status Member
 
Hello Pivert,

Indeed, with the other commands I tried as well as the one you suggested, I get the same error message:

Run-time error '1004'
Unable to read the Vlookup property of the WorksheetFunction class

Then it sends me to this part of my code that looks for a cell value based on my textBox18.

Private Sub TextBox18_Change()
Dim myRange As Range
Set myRange = Worksheets("variablesX").Range("A8:I52")
TextBox2.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 2, False)
TextBox10.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 3, False)
TextBox3.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 4, False)
TextBox11.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 5, False)
TextBox4.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 6, False)
TextBox12.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 7, False)
TextBox5.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 8, False)
TextBox13.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange, 9, False)

Dim myRange2 As Range
Set myRange2 = Worksheets("Ranges_2").Range("A2:I46")
TextBox14.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange2, 3, False)
TextBox15.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange2, 5, False)
TextBox16.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange2, 7, False)
TextBox17.Value = Application.WorksheetFunction.VLookup(TextBox18.Value, myRange2, 9, False)


End Sub


I tried looking for other solutions and asking questions on other forums, but it’s getting complex ;-)


Thank you for your availability!

Lucas
0
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello f894009,

Here is the file

https://www.cjoint.com/c/HHzpCjKEQUQ

Ofeu,

It works very well, no error messages on my end


Execution error '1004'
Cannot read the Vlookup property of the WorkSheetFunction class


this has nothing to do with the initial request for deletion!

try using the tags as I indicated, the codes are much more readable!



@+
0
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Re,

When you have the error, click on debugging and hover the mouse over TextBox18 to see its content.
0
Ofeu
 
I tried to find the error.
On my
Private Sub TextBox18_Change (I changed "Change" to "AfterUpdate") and it works the REFRESH button, but:

My textBoxes (2 to 17) disappear.
Maybe I need to work on my .visible functions!

Additionally, when I click REFRESH, my dropdown lists no longer display the data.

I am trying to find a solution!!!

Thank you
Best regards
Lucas
0
Answer 5 / 5
archer
 
Hello
you need to create a routine as simple as this 

Dim Ctrl As Control
 For Each Ctrl In Me.Controls
 Select Case Left(Ctrl.Name, 4)
 Case "Text"
 Ctrl.Value = ""
 Case "Comb"
 Ctrl.ListIndex = -1
 End Select
 Next Ctrl


See you
Maurice
0