天道酬勤,学无止境

vba

Determining if a cell is linked to a QueryTable via VBA in Excel

I'm linking an Excel worksheet to a data source using QueryTables which works well. I'd like to introduce logic to check a given cell and determine if that cell is the top left corner of an existing QueryTable. This seemed trivial in concept, but has proven elusive in practice. If I try to check any properties of the cell that relate to QueryTable I get an error. i.e. rng.querytable throws error 1004 if rng does not link to a QueryTable. So I messed around with testing if rng.ListObject is nothing based on some discussions I found online. It turns out that rng.ListObject is something if the

2022-01-19 02:34:02    分类:问答    vba   excel

Listbox: add a combobox as Item?

Is it possible to make every item of a listbox as a combobox? I need this because i made the listbox as checkable and then I need to make the user to choose from different options for every element of the list. Thanks!

2022-01-19 01:20:59    分类:问答    vba   excel

'With Worksheets("xxxx")' works only when "xxxx" is the active worksheet

I'm pretty new to Excel VBA. So far I've read and learned a lot on this site, but haven't found a solution for my problem. As part of a macro I have the following code: With Worksheets("Oracle") On error resume next ActiveWorkbook.Names("bron").Delete ActiveWorkbook.Names.Add Name:="bron", RefersTo:= Range("A1", Range("A1").End(xlToRight).End(xlDown)) .Cells.Select With Selection.Font .Name = "Verdana" .FontStyle = "Standaard" .Size = 8 End With .Range("A1", Range("A1").End(xlToRight)).Font.Bold = True MsgBox "Tabblad ‘Oracle’ is klaar!", vbOKOnly End With I understand that with the first line

2022-01-19 01:13:32    分类:问答    vba   excel   reference   worksheet

'CREATE TABLE' generates Run-time error '3290'

I have a syntax issue in the first CREATE TABLE statement. I'm receiving the following VBA error: Run-time error '3290' The goal is to move the distinct data to a new table dependent on values in specific columns. Afterwards the original table is cleared, and every distinct value will be inserted again. The temporary table will be deleted afterwards. ' ** Issue here ** ' db.Execute ("CREATE TABLE tTemp AS (SELECT DISTINCT History_Date, Sedol, Selskabsnavn, MarketCap, JQScore, JQ_Rank, Value_Rank, Quality_Rank, Momentum_Rank FROM JQHistory)") db.Execute ("DELETE * FROM JQHistory") db.Execute (

2022-01-19 00:33:48    分类:问答    sql   excel   vba   ms-access

VBA in Word: Programmatically add content control with a style

When programatically adding a rich text content control to a Word .docm using VBA, is there a way to set a style for the contents? As a comparison, if I create a content control manually using the Word Developer toolbar, I can select "Use a style to format contents" in the properties dialog for the content control. The result I want is the same as if I did it that way, except I need to do it in code. Here's the code I have that adds the content control, it's triggered by a command button click that does a few other things as well: Private Sub selConcept_Click() ActiveDocument.InlineShapes(1)

2022-01-18 18:55:59    分类:问答    vba   ms-word   word-style

Loop Through CheckBox Controls in VBA UserForm

I have a user form in Excel VBA with a check box for each month. Selecting one or more cause the required month to be shown on the sheet, I copy-pasted the code 12 times and it works but I'm sure there is a better way doing it with a For loop. This is a part of my code (it goes on 12 times): If CheckBox1.Value = True Then ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = True Else ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = False End If If CheckBox2.Value = True Then ActiveSheet.PivotTables("PivotTable1").PivotFields

2022-01-18 18:46:32    分类:问答    excel   vba   loops

Pivot Table: Detect When Pivot Field is Collapsed

For my data shown in a pivot table, I have chosen to apply conditional formatting to certain portions of the data table to highlight values within certain ranges. It was interesting to figure out how to highlight the 2nd level row data differently from the subtotal data, but I was able to work it out. My VBA fires using the Worksheet_PivotTableUpdate event so that whenever the user changes the pivot table fields, the conditional formatting is updated appropriately. This method continues to work when some of the sections are collapsed: My run-time error occurs when all of the top level sections

2022-01-18 18:11:49    分类:问答    vba   excel   pivot-table

Can't eliminate Access corruption

My firm's Access database has been having some serious problems recently. The errors we're getting seem like they indicate corruption -- here are the most common: Error accessing file. Network connection may have been lost. There was an error compiling this function. No error, Access just crashes completely. I've noticed that these errors only happen with a compiled database. If I decompile it, it works fine. If I take an uncompiled database and compile it, it works fine -- until the next time I try to open it. It appears that compiling the database into a .ACCDE file solves the problem, which

2022-01-18 17:45:46    分类:问答    vba   ms-access   ms-access-2007

How do I sign an XLA (Excel add in)?

And is is possible to do that from code?

2022-01-18 17:25:13    分类:问答    vba   code-signing   xla

VBA CopyPicture fails on locked screen ("error 1004 cannot empy clipboard")

I have a simple VBA code that copies range (some charts and tables) using CopyPicture and saves it as .gif file. Everything works perfectly when the screen is unlocked but when it's locked, Excel throws 1004 error: "cannot empty the clipboard" from time to time (about 40% of attempts). I think I've tried everything: - Every solution given in similar thread: CopyPicture method of range class failed - sometimes - Doing some error handling by waiting 5s and trying to repeat (Application.Wait (Now + TimeValue("00:00:05"))) - Turning screenupdating to true Application.Screenupdating=True - Clearing

2022-01-18 14:58:13    分类:问答    excel   vba