User Profile
Cangkir
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Macro to select multiple values in Data Validation List
JoaoTeixeira If you're willing to use an add-in, you can try a free Excel add-in called "Search deList". You can find it here: https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/ This new version has some additional features, some of them: Several ways to search, like using AND or OR or LIKE operator , with or without keyword order. Sort the list by original order or ascending order. Widen or shorten the combobox width at run time. Insert multiple entries into the cell.Re: Paste TO visible cells only in a filtered cells only
If anyone still has problem with this, please check out this article: https://www.mrexcel.com/board/threads/excel-vba-easy-way-to-paste-to-visible-cells.1239792/ it shows you how to make copy-paste visible cells functionality available in any open workbook.Re: Copying/Paste Filtered Cells
J199430 Excel does not provide a direct method for copying and pasting from visible cells to visible cells. So here's a method using VBA. Please check out this article: https://www.mrexcel.com/board/excel-articles/excel-vba-easy-way-to-paste-to-visible-cells.70/ And you can put the code in PERSONAL.xlsb and assign a toolbar button to it. The benefit of doing it this way: The macro is available on any open workbook & easy to access (i.e. via a ribbon button) You don’t need to put the macro on all your workbooks, just on PERSONAL.xlsb.Re: VBA Dropdown, not working on older versions of excel
BlakeBessigner Searching and creating the dropdowns manually via an add-in would add further steps for the user and overcomplicate a process that is intended to be as easy as possible, The title is somewhat misleading. In reality, the add-in is not for creating data validation; rather, it is designed to automatically generate a searchable combobox based on existing data validation. The list in the combobox is sorted and unique.Re: VBA Dropdown, not working on older versions of excel
BlakeBessigner BlakeBessigner wrote: Mainly looking for What version of Excel is causing this issue and how to work around it. The VBA code pastes all the devices I need to a column on the "Dropdowns" sheet, then it uses the Sort, Unique, and Filter functions to create the array of devices the dropdowns point to. When I run this on my machine it works 100% of the time, but when this code is ran on my Boss's machine it fails with an error. Try using "Search_deList_v2.1", it's a free Excel add-in, it works on Excel 2007 or later. Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT arrow will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list. It also gives you a unique & sorted list. By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx. This new version has some additional features, some of them: Several ways to search, like using AND or OR or LIKE operator , with or without keyword order. Sort the list by original order or ascending order. Widen or shorten the combobox width at run time. Insert multiple entries into the cell. You can find it here: https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/Re: Paste TO visible cells only in a filtered cells only
rajm189 hi this VBA is working fine, but it’s taking time if we are using in larger number of cells, can we get quicker VBA ? Sorry for the late reply. It depends on your data. About how many rows is your data? Do you copy from filtered range? Does your data have multiline cells?Re: The annoying excel scientific notation
cchai Assuming the formula shows original number, maybe this way: Use macro to add apostrophe (') in front of the numbers. Something like this: Sub to_Text() Dim c As Range For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp)) c = "'" & c.Formula Next End Sub Before AfterRe: How can I create multiple dependent drop down lists
Lycias Here's an example of 3 level dependent data-validation using the new function in Excel 365. The functions used are: SORT, UNIQUE, FILTER, XLOOKUP & TEXTSPLIT https://www.mrexcel.com/board/threads/3-or-more-dependent-data-validation-with-vba-easy-to-set-up.1185808/post-6010313Re: Can not type when opeing a dropdownlist (AutoComplete for Drop-down List)
If you're willing to use an add-in, try a free Excel add-in called "Search deList" to get searchable data validation. You can find it here: https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/Re: Dropdown List with "description" and actual "value"
sandrosg Here's another option, using vba & data validation: Helper range: $G$6:$G$8. Data validation formula: =$G$6:$G$8. On the Error Alert tab, uncheck mark on "Show error alert after invalid data is entered". In Sheet1 code window, put this code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo skip: If Target.Cells.CountLarge > 1 Then Exit Sub If Len(Target) = 0 Then Exit Sub If Not Intersect(Target, Range("F14:F16")) Is Nothing Then 'Range("F14:F16") is range with data validation Dim tx As String, c As Range, v tx = Target.Value If Len(tx) > 0 Then If InStr(tx, " : ") Then v = Split(tx, " : ")(1) Else v = Target.Value End If Application.EnableEvents = False 'Range("F6:F8") is col Distance Set c = Range("F6:F8").Find(What:=v, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then Target = v Else MsgBox "Wrong Entry" Target.ClearContents End If Application.EnableEvents = True End If End If Exit Sub skip: Application.EnableEvents = True MsgBox "Error number " & Err.Number & " : " & Err.Description End SubRe: Auto complete text in a drop down list cell
Heather_Hagart In case anyone needs it, I just shared a new version of "Search deList" add-in, i.e Search_deList_v2.1. Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list. By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx. This new version has some additional features, some of them: - Several ways to search, like using AND or OR or LIKE operator , with or without keyword order. - Sort the list by original order or ascending order. - Widen or shorten the combobox width at run time. - Insert multiple entries into the cell. You can find it here (please check post #81): https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/post-5978517Re: Autocomplete dropdown
Faradn Try a free Excel add-in called "Search deList". It works on Excel 2007 or later. I just released a new version. You can find it here: https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/post-5978517
