Questions tagged [vba]
28291 questions
1
votes
0
answer
8
Views
Using VBA - Error closing some instances of Acrobat
I have this code that will go though a column of URL's that open a PDF file. My code opens the URL, saves it as a PDF using send keys, opens the file, searches the file for words from a column from excel, if it finds a match, it then places the word in the cell next to the URL. After this, it goes t...
0
votes
0
answer
2
Views
Is it possible to make a dynamic sql statement based on combobox.value in access?
I made a form in access with 2 different comboboxes. The user of
This tool can choose in combobox1: the table (which has to be filtered) and the second combobox2 is the criteria to be filtered( for example Language= “EN”) and the output of this query has to be located in tablex.
The problen wha...
1
votes
2
answer
1.1k
Views
Turkish Characters in the VBA editor
I have a problem with Turkish characters in my simple VBA code. Whenever I write some text in my module in Turkish characters (e.g. 'ş, ə, ç, ğ, ö, ü, ı"), they change to unknown letters.
I want to change "Eight" to "Səkkiz","Five", "Beş","Three" "Üç" etc.
1
votes
2
answer
9k
Views
SSIS Assigning value to Variable using Script Task
I am trying to assign a value to a variable from a flat file in an SSIS package. The script i am using is below
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
#End Region
' This...
1
votes
1
answer
221
Views
How to specify range instead of offset , excel VBA?
I want to give a range instead of offset in the following code. following code copies the color from range(C5:F11) to the offset i set but i want to specify a range like range(M5:P11). I tried it simply replacing offset by range but it does not work properly. Please help
Sub MatchColors2()
For Each...
1
votes
2
answer
1.4k
Views
vba replace nested if statement
Is there a way to make this more efficient? I find it a bit ugly.
I want to verity if there is a next "deck", if so, execute some for it, until deck8
here is my code:
If deckNum > 0 Then
'Execute code for deck 1
If deckNum > 1 Then
'Execute code for deck 2
If deckNum > 2 Then
'Execute code for deck...
1
votes
1
answer
440
Views
Creat a dynamic Loop in VBA with array
I want to create a loop for time series in VBA , so that the LINEST would be done in every row, data for LINEST have to be dynamic, e.g.
=Linest(C6:N6; Q1:AB2; TRUE) ==> result array of thre
next row =Linest(C7:N7; Q1:AB2; TRUE) ==> result array of three
next row =Linest(C8:N8; Q1:AB2; TRUE) ==> res...
0
votes
0
answer
3
Views
Outlook - warning if email has been sent before this month
Is there a way to set up a code so when I send an email from a template to someone (often if I'm contacted with the same over and over again) it automatically checks if I already sent it to them within the last month.
Right now I have made a warning system for myself, that shows a message box when I...
0
votes
0
answer
7
Views
Why does the ImportXML Method leads to a runtime error 9?
The Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = [Namensfilter].Row And Target.Column = [Namensfilter].Column Then
Dim Displayname As String: Displayname = [Namensfilter].Value + "*"
Dim Map As XmlMap
Set Map = ActiveWorkbook.XmlMaps(1)
Dim urlXML As String
urlXML = "htt...
0
votes
0
answer
4
Views
Coco dataset to voc dataset for darkflow
I am working on a project of object recognition in darkflow andI want to train it with the coco dataset. Darkflow can only work with the voc dataset so far I know. So I am trying to convert the coco dataset json to multiple xml files darkflow need. I want to create a script in vb.net because I know...
1
votes
2
answer
578
Views
How can I use wildcards with MyVlookup function?
When calling the below function in Excel by using =MyVlookup(B1,Sheet1!A:A,1) I get what I want.
Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
Dim cl As Range
For Each cl In c.Columns(1).Cells
If UCase(Lval) = UCase(cl) Then
MyVlookup = cl.Offset(, oset - 1)
Exit Functio...
1
votes
2
answer
1.8k
Views
Excel Generating a formula based sequence based on levels [closed]
What I am trying to achieve is the following
ID Name Level
1 ABC 1
11 ABC 2
11001 ABC 3
1100101 ABC 4
110010101 ABC 5
11001010101 ABC 6
11002 ABC 3
1100201...
1
votes
1
answer
2.3k
Views
Create a new Ribbon tab with vba that only appears selecting certain shapes
I want to make appear a new ribbon tab that only appears when I select a shape that I want. I know to make normal tabs using Custom UI Editor For Microsoft Office or also with VBA using the following example:
Dim oToolbar As CommandBar
Dim oButton As CommandBarButton
Dim MyToolbar As String
' Give...
1
votes
3
answer
5.1k
Views
Delete Row from Array
I am trying to go through an array to find duplicate entries in a single column of that array and delete the entire row.
I am getting figuring out rangeStart, rangeEnd, and lastrow above this and that part is working fine.
data = Range(rangeStart, rangeEnd)
For i = lastrow - 1 To 2 Step -1
If data(...
1
votes
1
answer
807
Views
Restore default ribbons with vba (Using a custom UI)
Sorry, I know this case has been discussed often, but I didn't find the perfect solution.
In my access database I use a custom ui per USysRibbons, this works great. The default UI is not shown in User mode, but there should be a button that simply restores the default UI. What would be the best sol...
1
votes
2
answer
13.6k
Views
Applying multiple filters on a form/report
I am looking to be able to allow users to apply multiple filters on a form/report. Let me explain further.
I have a subform (embedded in a form) which is already filtered by an option box which I have created. I would like to add ANOTHER Option box.. Is it possible.
To simplify my requirement let me...
1
votes
2
answer
105
Views
What is a Compact way to Refer to a Set of Non-Contiguous Rows?
The below code seems inelegant and wasteful. Is there a more compact notation for referencing all those rows?
Sub HideEBRedData()
'un/hide EIRP Budget rows with redundant path data
Application.ScreenUpdating = False
Set EIRPBudget = Sheets("EIRP Budget")
With EIRPBudget
.Rows(29).Hidden = Not .Rows(...
1
votes
1
answer
739
Views
What is the line-continuation character in vba? [closed]
How to write a string manipulation in multiple lines like this in VBA? ( " _" is not working here)
szStr= szStr & "1=" & szVar1 & ";50=" & szVar50 & _
";79=" & szVar71 & ";7971=" & szVar7971 & _
";7423=" & szVar7423 & ";9523=" szVar9523 & _
";1138=" & szVar1138 & ";109=" & szVar109 & _
";9526=" & sz...
1
votes
1
answer
1.5k
Views
Programmatically “Set Regarding” - Dynamics 2011 Outlook CRM Client - VBA
I'm writing a VBA macro which runs in Word 2010 on a system which has Microsoft Dynamics CRM 2011 for Microsoft Office Outlook client installed (and connected to a CRM 2011 on-premise server).
The macro is aware of the GUID for a CRM Incident and as well as doing other tasks should create an email t...
0
votes
0
answer
16
Views
VBA: Paste function causes 1004 error after the paste range is cleared
I have a problem with my code, when i try copy some piece of data from one sheet ("Import") to another ("TIs"). There is a range in the second sheet, that is cleared before the data is copied. When I try to paste it, I get a
Error 1004 "Application-defined or Object-defined error"
Sub Data()
Sheets(...
0
votes
1
answer
16
Views
Getting number of duplicates before removing them
I just want to get the number of duplicates removed when using the .RemoveDuplicate function in excel vba. Is there anyway to get the precise number of how many duplicates are removed when using the excel function?
Code:
rangeofws.RemoveDuplicates Columns:=1, Header:=xlYes
rangeofws is just a range...
0
votes
0
answer
8
Views
How can i automate delete e-mails in Outlook with excel
Given an excel, with a column. This column conatins strings.
The puspose: If a receveid e-mail's subject does not contains one of the string which are in the excel column -> delete the e-mail.
For example:
Excel column:
"asd"
"fgh"
Received e-mails subject:
1st e-mail subject: "Holiday in Canada" -...
1
votes
1
answer
128
Views
Applying same method to multiple objects
I am curious to know whether there is a way to apply the same method to multiple objects. In other words, I'm looking for something that is, in a way, the inverse of a "With" statement.
I know that using "With" statements, I can apply multiple methods to a single object:
With ITEM_RNG
.ClearContents...
1
votes
2
answer
6.5k
Views
How to set warnings off in excel application from access vba
I'm opening excel workbook from access, after writing contents from access to excel, i'm using xlobj.save to save the workbook. Excel application is giving some warnings says this workbook already exists do u want to replace it. How to disable such warnings from access.
I'm using DoCmd.SetWarnings...
1
votes
1
answer
1.4k
Views
ms access form closing ask save yesnocancel
I have put the VBA in Unload Form Event in ms access 2010
Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."...
1
votes
1
answer
4.8k
Views
VBA - Multiple series to a chart
I'm trying to make add two series to a single XYscatter chart via loop. Currently, my code creates two charts. The X values are constant but the Y values change so I added them to an array to preserve them.
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim thearray(9) As Dou...
0
votes
0
answer
6
Views
Excel stops running protected workbook, runs fine if unlocking it
I have a tool, which uses VBA.
As the tool has company secrets, the project is password protected to avoid the info getting into the wrong hands (to some extent). Therefore, as soon as anyone wants to "view code", they have to put in the password.
The tool has a simple GUI, which just loads values f...
1
votes
2
answer
2.7k
Views
Find consecutive cells based on a value
I have a bank of data that is updated monthly. I need to analyze said data to determine first all values below 64, then I need to look for two consecutive instances of this event. Once found, I would like a value returned to indicate this vendor is a problem.
The second piece of the puzzle is that...
1
votes
1
answer
3.3k
Views
How use the combobox keydown event without selecting the listed item
I have in my Excel VBA project, a combobox which uses a range of cells as it's list of items. I have used a filter in it, so that whenever a value is entered, the list shrinks down to the items containing the string, and dropdown list is shown. However, the problem appears, when the dropdown is show...
1
votes
3
answer
990
Views
Delete rows from table if entries duplicate another table
Been a while since I worked with VBA so bear with me as I may be a little bit rusty.
I have 2 tables. One is what I call a "Deleted" list with a few columns and the other is a more detailed list with all details. What I want to do is look up items on the "Deleted" list (see Table 1) and if all the...
1
votes
3
answer
4.7k
Views
Get reference to next visible cell in autofiltered table
I have a UserForm that sits on top of my spreadsheet and simply displays information from the row containing the currently selected cell. Buttons on the form allow the user to move up/down the spreadsheet row by row. For example, when the "next record" button is clicked, the code executed is somet...
1
votes
2
answer
4.4k
Views
Saving an Excel File as PDF on both Windows and Mac
I have created a macro to export my sheet as a PDF however some users in the company use Mac OS. When these users attempt to save, it gives them an error. How do I allow both Win and Mac users to use the same PDF export?
Here is my current code:
Sub CreatePDF()
Dim wksSheet As Worksheet
Set wksShe...
1
votes
0
answer
15
Views
Write TXT file with VBA - File is interrupted
I have a vba macro that writes a txt file in "datevalue" format (that is, year tab month tab data) before running the X13 ARIMA SEATS.
It has always worked, but recently it works only some times. When I looked at the .txt file it generated before seasonally adjusting it, it was interrupted in the m...
0
votes
1
answer
13
Views
Using Excel VBA to use a search box, trigger click event and retrieving results?
After searching every topic I could find on this subject and running into error after error, I've come here hoping somebody can assist me.
I am trying to utilize this website, https://azredistricting.org/districtlocator/,
to search addresses, which return a legislative / congressional district.
I...
1
votes
1
answer
75
Views
Run Macro Every 2 hours Only if previous run is complete
I've set a macro to run every 2 hours by using:
Application.OnTime (TimeSerial(6, 0, 0)), "AutoUpdate"
However there is an issue sometimes as occasionally the macro will spill over 2 hours and try to run again whilst the previous macro is still running, causing it to crash. Does anybody know of a w...
1
votes
2
answer
3.1k
Views
VBA dynamic checkboxes positioning
I have an Excel VBA userform where I am attempting to add a number of checkboxes according to the number of items in a given dictionary.
This works, but each of my checkboxes is added to the top left corner, on top of each other. How can I place them so that the newest one is immediately below the p...
1
votes
2
answer
10k
Views
Selecting and copying Outlook email body with a VBA macro
I'm a beginner to VBA macros in Excel, and this is the first attempt in Outlook, but here's what I am trying to do:
In Outlook 2010, assign a macro to a button that, when pushed,
Gets the entire body of the active email
Copies the body including all formatting and html to the clipboard
Opens a new w...
1
votes
1
answer
1.2k
Views
ADODB error handling variable not setting
I building an ADODB error trap but for some reason, by errSQL.Number and errSQL.Description both give me a "Object variable or With Block variable not set." error....here is my code so far...I have the active x object enabled and I thought that .number and .description are correct...any help would b...
1
votes
2
answer
36k
Views
Format number in VBA Textbox
I have a texbox in a VBA userform in which the user will input numbers and those numbers will be tranfered to a excel spreadsheet for calculation.
I want that when the user input numbers in the Textbox it does display it as a number.
Per example: 2000000, I want it as 2,000,000.00 in the Textbox.
Is...
1
votes
4
answer
939
Views
Excel 2003: Active-X bug from Dec 2014 “update” — still broke. What else to try?
Situation: big huge Excel VBA project, running in Excel 2003. Tons of code, been running for years. The December 2014 update from Microsoft that broke Active-X controls for everybody, just got installed on my machine, and hey, it broke for me too. Okay, StackOverflow to the rescue, found the solu...