Posts by J. L. Muller • 555 points
45 posts
-
1
votes1
answer118
viewsA: How to return a Promise from the resolution of others?
I studied the files a little better and I was able to realize the solution: encapsulate all the content in a new file and call the solution within Promise.all(), when the results have already passed…
-
2
votes1
answer118
viewsQ: How to return a Promise from the resolution of others?
I’m relatively new to this Promises, despite having used them numerous times, especially in AJAX requests. I’m building a front-end application that runs on Sharepoint and, to get a lot of…
-
0
votes1
answer3790
viewsQ: Check if a Listbox item has been selected
I have a list ListBox1 q a check box ChckBox1 in a Userform. I want to activate or inactivate the checkbox (CheckBox1.Enabled = True/False if an item in the list is selected. The check-box is…
-
0
votes1
answer1926
viewsQ: Count the number of selected items in a Listbox
I have a list ListBox1 in a userform configured for multiple item selection. There is some function, statement, or way to return the number of selected items?
-
2
votes2
answers2141
viewsQ: USERFORM: What is the difference between Initialize and Activate routines?
What’s the difference in using Initialize and Activate as a Userform boot routine? Private Sub UserForm_Initialize() Private Sub UserForm_Activate()…
-
2
votes3
answers962
viewsA: How to make the automatic recalculation of Excel trigger a routine in VBA?
Use the event Calculate for the Worksheet on which the button and graph is located, thus: Private Sub Worksheet_Calculate() Call Sub_Usada_no_Botao End Sub So every time some calculation occurs in…
-
1
votes2
answers792
viewsA: USERFORM: I can’t remove items from my Listbox (Error 80004005)
By popular a Listbox with the Rowsource property you cannot edit the items within the list, especially removing an item. You would need to reconstruct the list with the statement .AddItem to…
-
2
votes3
answers962
viewsA: How to make the automatic recalculation of Excel trigger a routine in VBA?
Unfortunately there is no simple way to do this; it is necessary to break your head and devise a system of on/off of the resource Application.Calculation. The modes of this feature are:…
-
0
votes1
answer6730
viewsA: Excel validation list - runtime error 1004 - Application definition or object definition error
Hello, friend. I thoroughly tested your code and now I was faced with the mistake, now I didn’t. I took, well more than 5 min, hehehehe... The error occurs when the validation insertion is disabled,…
-
0
votes2
answers3298
viewsA: How do I view VBA button properties of an excel spreadsheet?
Your problem is a bit confusing, but if you are trying to find the code that a button runs, maybe it has been associated with a specific event about that button/controller. So maybe what you’re…
-
0
votes2
answers3298
viewsA: How do I view VBA button properties of an excel spreadsheet?
There are 2 types of buttons to be placed directly on the spreadsheet. For both you access the properties by right-clicking on them. To the button of Form Control, you access the properties by…
-
1
votes2
answers792
viewsQ: USERFORM: I can’t remove items from my Listbox (Error 80004005)
I have a Listbox in a Userform that I "reconstruct" based on a Textbox that I use as a filter. The idea is to bring all items to the list with Rowsource and remove all items that do not contain…
-
1
votes1
answer1070
viewsQ: VBA: Declare variables online
To declare variables in VBA, I always used the following method: all variables of the same type I put in the same line and declared their type only at the end, as in the example below: Dim…
-
0
votes2
answers9083
viewsA: VBA function to make text uppercase
You can force the user to type only in uppercase letters via Data Validation. Select the range and click Data Validation. Under 'Allow', select the 'Custom' option and in the 'Formula' field type…
-
0
votes2
answers9083
viewsA: VBA function to make text uppercase
Via VBA, you can associate the following code with the`Change`` event in the Sheets module: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target,…
-
0
votes0
answers344
viewsQ: USERFORM: Move Listbox item up or down
I have a Listbox in a Userform that reflects a list of the order of display of the items in a Dashboard. I want the user to be free to change the order of these items by moving them down or up in…
-
0
votes1
answer113
viewsA: Prevent the PROCV formula from returning an error
For that there is the formula SEERRO. In it you can set up a condition if you get an error. The first argument is where you write the formula that can possibly generate an error, and in the second…
-
0
votes1
answer113
viewsQ: Prevent the PROCV formula from returning an error
Is there any configuration in Excel or some way to make the PROCV formula, when it does not find the searched value, return empty ("") or 0 instead of the error "#N/A"?
-
3
votes3
answers13786
viewsA: VBA: Use Excel to rename files in a folder
Below follows code that applies what you want to all files of a given folder, regardless of the extension, bypassing subfolders: Sub Renomear() 'Dimensionar variáveis Dim MyFolder, MyFile, NewName…
-
3
votes3
answers13786
viewsQ: VBA: Use Excel to rename files in a folder
I’m thinking of using Visual Basic from Excel to standardize the name of 6000 files in a given folder. These files are mixed between photos and documents, so there are a variety of extensions (DOC,…
-
2
votes1
answer29901
viewsA: Excel formula to search part of the text in a table
You can use the PROCV asterisked. Assuming that the typed field is in cell A1 and its table of banks and codes is in the range A4:B50, for example, the formula would look like this:…
-
1
votes1
answer2823
viewsQ: VBA: Putting a space before or after uppercase letters in a given text
I have a string of texts that represent customer names, but which are without the proper spacing between each word/name (for example: Carlosalbertogomes). However the words can be differentiated…
-
1
votes2
answers2802
viewsA: Excel/VBA file more than duplicates in size for no apparent reason while being saved
It is hard to know exactly what is happening to your file. I have had similar situations and there are many variables that have to be taken into account to try to reduce the file size. I will try to…
-
3
votes2
answers17620
viewsA: How to remove semicolon character with excel
Use the formula TEXTO to reconfigure the formatting of numbers. Since you want to remove the dots (thousand separators) and commas (decimal places), your formula will look like this: =TEXTO(K2;"0")…
excelanswered J. L. Muller 555 -
0
votes1
answer6622
viewsA: Macro VBA for inserting images automatically based on a column of codes
If I understood correctly, you have this table in a spreadsheet (in VBA I considered it to be in the range A2:B6) and what you call code refers to the image file name without its localized extension…
-
0
votes1
answer211
viewsA: When I refer more than one column using Formular1c1
Although it is not very detailed and explained what the problem is and what you want, I conclude the following: It seems to me that you recorded the macro and you are using relative references,…
-
0
votes1
answer32
viewsA: Association condition E as formatting between numerical ranges
Reassess the second logical argument of the function =E ([8>=$R1744<=12,99]). If we separate into two separate equations we have: R1744 <= 8 R1744 <= 12,99 Kind of redundant, don’t you…
excelanswered J. L. Muller 555 -
0
votes1
answer136
viewsA: Rename a variable and update it in the rest of the project
there is no specific function or tool for this, but the most common is to use the famous "Find/Replace" (Ctrl + F). When you have the VBA open, click on Ctrl+F and then on the button…
-
0
votes1
answer136
viewsQ: Rename a variable and update it in the rest of the project
Have some easy or automatic way to update the rest of your code when renaming a particular variable on the line Dim? What do I have: Dim ABC As String If Range("A2").Value = 1 Then ABC = Range("C2")…
-
0
votes1
answer604
viewsA: Concatenate Userform text and insert as link in a cell
The problem is that you are concatenating a formula with other texts and, as the formula transforms the selected content into a link, things are getting confused. You should do something like this:…
-
0
votes1
answer604
viewsQ: Concatenate Userform text and insert as link in a cell
I’m trying to feed a table with 3 information inserted from the execution of a Userform and, at the end, make a direct hyperlink in the cell with the information inserted. The information is as…
-
0
votes1
answer3868
viewsA: Update all Dynamic Tables when selecting a Worksheet
Unfortunately it is not possible to assign any action to all Tables pivots on a single line, as if they were a single object. You would have to make a loop to update them one by one, so: Private Sub…
-
0
votes1
answer3868
viewsQ: Update all Dynamic Tables when selecting a Worksheet
I need all the dynamic tables in my spreadsheet to be updated every time I select one of my worksheets that serves as Dashboard. I used the most logical code, but I think something is missing, since…
-
2
votes2
answers4543
viewsA: Handle spreadsheet with userform loaded (excel vba)?
Yes, it is possible. Mark the property ShowModal userform as False, or change this property via VBA when you want (Me.ShowModal = False). This allows you to manipulate the spreadsheet while the…
-
0
votes2
answers915
viewsA: Create filter based excel table
Using a Dynamic Table would not help you? So you can put formulas next to the Dynamic Table and not have the reference problem or hidden lines when filtering. Also, you can insert Slicers to make…
excelanswered J. L. Muller 555 -
1
votes2
answers915
viewsA: Create filter based excel table
Copy and Paste doesn’t solve your problem? If so, use the command to select only visible cells: Select your entire table or columns you want to consolidate your new list. Filter by 'Florida' and…
excelanswered J. L. Muller 555 -
0
votes1
answer86
viewsA: Excel spreadsheet with unreadable cells when filtering
I’m sorry, buddy, but you weren’t very clear in presenting your problem, telling me what the problem is and what the outcome is. However, let me know if this helps you in any way: The formula…
-
1
votes2
answers1433
viewsA: Excel move un-duplicated values between two columns to a third
Does a PROCV combined with SE and SEERRO not solve you? =SE(SEERRO(PROCV(A1;B:B;1;FALSO);0)=0;A1;"") Explanation: if PROCV returns an error indicating that the value in A was not found in B, the…
-
0
votes2
answers68
viewsA: Conditions if nested
I believe that the desired formulas are these: =SE(E(A2>=1;A2<=3);Z2;0) 'cenário 1 =SE(E(A2>=4;A2<=6);Z2;0) 'cenário 2 =SE(E(A2>=7;A2<=9);Z2;0) 'cenário 3 =SE(A2>=10;Z2;0)…
excelanswered J. L. Muller 555 -
0
votes1
answer685
viewsA: Import workbook from another worksheet to a new workbook in the current worksheet
I believe that the problem lies in the information inserted in column "O": reading the actions performed by the code, everything indicates that the action Workbooks.Open will try to open a…
-
1
votes2
answers1389
viewsA: Optimization in VBA code?
If the columns are in different order, the code would have to make a loop even, from column to column: Sub Percorre() 'Desabilitar recursos desnecessários Application.ScreenUpdating = False…
-
2
votes2
answers1389
viewsA: Optimization in VBA code?
For any code optimization work, one of the basic actions is the maximum reduction of actions (and lines). One of the main actions that can be discarded is object selections and ranges (the blessed…
-
1
votes1
answer1024
viewsA: Excel VBA check the contents of two cells
I suggest you create a function with the rules of character replacement with accents. I don’t know how you wrote this code, but I imagined something like this, structured as a function for better…
vbaanswered J. L. Muller 555 -
0
votes2
answers4697
viewsA: VBA: Filter multiple items of a Dynamic Table containing certain text
To filter multiple items in a Dynamic Table, you must read them item by item and mark them as visible or not. And to compare texts contained in others, one should use the statement Like. Thus, the…
-
0
votes2
answers4697
viewsQ: VBA: Filter multiple items of a Dynamic Table containing certain text
I am trying to run a code that shows all the items of a Dynamic Table that contain a certain text. I imagined that only using asterisks (*) before and after my keyword I could get results, but the…