In my case following helped: Save file as .xlsx (macro-free) – all macros would be erased while saving; Open source file with macros and copy modules to the .xlsx file; Save file as .xlsm – full recompile performed. Afterwards everything started working normally. I had file with 200+ sheets and 50+ macros and posting comments … Read more
You need to change the name of the variable in a declaration line (Dim statement for example) somewhere (anywhere, actually). This is one of the most annoying “features” of the VBA IDE (as if version control weren’t already hard enough with VBA, the constant case-changing drives me batty). Variables have their case changed globally in … Read more
It is simpler just to run an exact copy like below to put the copy in as the last sheet Sub Test() Dim ws1 As Worksheet Set ws1 = ThisWorkbook.Worksheets(“Master”) ws1.Copy ThisWorkbook.Sheets(Sheets.Count) End Sub
Here is my solution, it works consistently and you don’t need to manually copy the sheets and code across to a blank workbook. I’ve tested this method on several corrupted workbooks that were giving me the “Automation error – Catastrophic failure” error upon launch. NOTE: Original corrupted file was saved as .xlsm Open a blank … Read more
I’m not very familiar with the shorthand method of referring to tables. If you don’t get an answer, you might find this longhand method, that uses the ListOject model, useful: Sub ListTableColumnMembers() Dim lo As Excel.ListObject Dim ws As Excel.Worksheet Dim lr As Excel.ListRow Set ws = ThisWorkbook.Worksheets(2) Set lo = ws.ListObjects(“tabWorkers”) For Each lr … Read more
Using Application.WorksheetFunction.VLookup( will break the code if the value is not found. Wrap it in vba error control. Function CCC(A As Range) Dim B As Variant B = -1 On Error Resume Next B = Application.WorksheetFunction.VLookup(A.Value, Sheets(“DAP”).Range(“$B$4:$X$7”), 5, False) On error goto 0 CCC = B End Function Another method is to Late Bind by … Read more
You can use Like operator (case-sensitive): Private Sub search_Click() For firstloop = 3 To 10 If Range(“G” & firstloop).Text Like name.Text & “*” Then MsgBox “Found!” Exit Sub Else MsgBox “NOT FOUND” End If Next End Sub for case-insensitive search use: If UCase(Range(“G” & firstloop).Text) Like UCase(name.Text) & “*” Then Also if you want to … Read more
Yes you can use the LIKE Operator with a wildcard “*”. Here is an example. I am assuming that the workbook is open. Sub Sample() Dim wb As Workbook Dim wbName As String ‘~~> “MyWorkbook2015” wbName = “MyWorkbook” For Each wb In Application.Workbooks If wb.Name Like wbName & “*” Then Debug.Print wb.Name With wb.Sheets(“My_Sheet_Name_That_Does_Not_Change”) ‘~~> … Read more
You would get a type mismatch if Sheets(name).Cells(4 + i, 57) contains a non-numeric value. You should validate the fields before you assume they are numbers and try to subtract from them. Also, you should enable Option Strict so you are forced to explicitly convert your variables before trying to perform type-dependent operations on them … Read more
Ok I was able to fix the issue using the following code protected override void WndProc(ref Message m) { const int WM_PARENTNOTIFY = 528; if(m.Msg == WM_PARENTNOTIFY && !this.Focused) { this.Focus(); } base.WndProc(ref m); } I added this function to my TaskPaneView which is simply a UserControl with that webbrowser child. I don’t have a … Read more