[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need help with VBA that If someone opens my workbook from Outlook then it should state that "workbook opened from outlook"
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I feel really stupid today. I have a simple, one-page worksheet and need to sort on the date (Column C). I need to understand how to fix the spreadsheet so it will sort. This will be a live document that is constantly changing and I would like a macro button to sort when necessary.
Pending-Projects-as-of-11-19-18-Rev.xlsm
0
I have an Add-in that is developed by someone it is free, but it is locked and it has a very annoying message everytime that i open the workbook. it gives me a message box that welcome bla bla bla.  I want to get rid of this workbook open event from this add-in while without modifying anything in the add-in itself, because it is locked.

what peice of code do i need to place in my PERSONAL.XLSB which is in the start up folder, in order to suppress the workbook_open event of the add-in.

the add-in file name is     REGRREAUTOMOBILE.XLAM

any help is appreciated.
0
Outline
The user requested an Excel macro to duplicate the sheet a certain # of times, and in one of the cells, they want the ticket # to increment from a certain starting number.

I am having trouble with serializing the number and keeping leading zeros, 6 digits which gets entered into a cell on the sheet.

I attached an example macro book and my whole macro is below.

Tried both Format() and Right() functions without success.

j = Right("000000" & (strStartingNumber + x), 6)
and
j = Format(strStartingNumber + (x-1), "000000")


Sub CreateDupeSheetsGenerateBulletinNo()
    Dim NumberSheets As Integer, strStartingNumber As String
    NumberSheets = InputBox("How Many Sheets? Enter a Number (1-50)", "How Many Sheets")
        If Not (NumberSheets) > 0 And (NumberSheets) < 51 Then
            MsgBox "Input not valid, please re-enter a positive number 1 - 50", vbCritical
            Exit Sub
        End If
    strStartingNumber = InputBox("What is the starting number?", "Starting Number")
    Dim x As Long
    For x = 1 To NumberSheets
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = x
        j = Format(strStartingNumber + (x - 1), "000000")
        Range("B3") = j
        Next x
End Sub

Open in new window



Can anyone help? Thanks.
ticketassignedtest.xlsm
0
How do you write a simple VBA coding for check boxes to filter data in a listbox.

On Form A I have several categories to check Category A, B, C that is base on yes/no. I want to make sure when I check Category A, just A shows on Form B. If left on unchecked then all categories show.
0
Access 2003
I set Breakpoints, but when I run the Subroutine, the program does stop at the Breakpoints
Very simple question:  How does one enable breakpoints
0
Good Afternoon,

I have a workbook in excel 2013 that I use to make a log of my duties each day.

At the moment to automate the form a bit, I am using some strings for some of the cells that I was able to figure out from online forums.

I would like the form to be a bit more automated and read online that macros can achieve this.

I have the workbook attached (OPLOG.xlsm) and would be grateful if someone can create a macro for me which will achieve what I want it to do.

Please note:

1.      The worksheet cannot be changed in any way and must remain the same with the layout, so adding more columns or moving things around is NOT an option.
2.      I would like all strings I have already in certain cells to be controlled by the macro (I will list what I want below). That way there will be no accidental deletion if I accidentally delete the code from the cell. So once the macro works there would be no need to have the strings in the cells anymore.
3.      I am not interested at this time of any suggestions or other applications that will accomplish this etc, I would just like to make this form work in the way I have listed below using a macro behind the scenes for it.
4.      All highlights, illustrations and red writing in the screenshots below are just there to give a clearer picture of what I want the macro to accomplish.

My requests for the Macro:

1.      Upon launch of workbook I would like the processing date to automatically set to today’s date, this …
0
I am not an SME in VBA. I understand the basics, the issue here is this code was written by a former employee.  It works in Windows 7 (Using Excel Object Library 14.0) and does not work on Windows 10.  I get the following error:

Run time error '9':
Subscript out of range

The error starts on line 154, but I have no idea why?  It is in bold within the code..

"wkb.Sheets(2).Range("A1:J1") = Array("Name", "Avaya Name", "Employment Type", "Tenure Type", "Inclusion Status", "Secret Code", "MRRs", "Combo CMRs", "Solo CMRs",
     " Avaya Total Hours")"
--------------------------------------------------------------------------------------------

Private Sub CommandButton5_Click()
Dim Wb As Workbook
Dim lastTeamRow, lastGreenReportRow, lastCanvasRow As Long
Dim GreenReportArray, GreenReportHistory As Variant
Dim c As Variant
Dim callArray(0 To 1) As Variant
Dim RphTenureTotal, RphNonTenureTotal As Double
Dim appExcel As Excel.Application
Dim wkb As Workbook
Dim index1, index2 As Integer
Dim FCMCallbackTotal As Long

CommandButton5.Enabled = False

Set Wb = ThisWorkbook
With Wb.Sheets("Teams")
    lastTeamRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With

Team = LoadTeam(ReportType.GreenReportTeam)

' create array for Green report per employee
ReDim ReportArray(1 To lastTeamRow - 1, 1 To 8)

' create array for Green Report Array
ReDim GreenReportArray(1 To 1, 1 To 16)

' populate Green Report Array date
GreenReportArray(1, 1) = Format(Wb.Sheets("RxConsult 

Open in new window

0
Hello,

My company's MS Access VBA software uses Azure Blob Storage and allows users to upload files from their computer to our blob storage. I have a user that requested for the file dialog box filter to default to what they last selected. Currently the filter always defaults to Image files and usually this user uploads PDFs. Is there anyway to get the filter to default to the last filter the user selected? Please see attached file to view the current code for the file dialog.

Thanks in advance!
File_Dialog_Code.PNG
0
Hello experts,
I have a list of task which are related to multiple phases
gantt2.png
I am looking for an smart way to display the information through an excel gantt chart bar.
Could you please share some  excel  template or provide some advices concerning this need.
Thank you in advance for our help.
0
CompTIA Cloud+
LVL 12
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I have some VBA code that opens up a url generated with variables which displays a table that can be copied and pasted into Excel. I need to do this for 22 airlines and 900 aircraft.  Everything has worked perfectly for the last year but the other day it broke down for a few of the airlines. The attached workbooks includes 2 of them for demonstration purposes.

As the workbook explains, if I hard code the url  for a specific aircraft (which I can't do for 900 aircraft), then it works. Hopefully, the problem is not a hidden issue in Chrome or flightaware for some airlines and there's a way to fix it within my code.

Note: you have to have Chrome installed on your computer for the code to work at all.

Thanks,
John
FlightawareGrab_EEQuestion.xlsm
0
Is it possible to determine if a given string occurs two or more times in a given cell, and if so then remove all occurrences of that string but one ? Ideally something as simple as an IF statement, like this:
Sub RemoveRNRDuplicates()
For Each cel Range([T6], [T10000].End(xlUp))
If "R/NR" occurs more than once then remove all other occurrences of "R/NR"
Next cel
End Sub

Open in new window

Thanks!
John
0
Good Afternoon,
I'm trying to compare data from an Excel Sheets, I have to compare rows with each other.
My data is like :
1 aaaaaaa 19.5  15          louis
2 aaaaaaa  19.5               louis
2 aaaaaaa  19.5 15 a      louis
3 aaaaaaa  19.5               louis

I need to verify that all columns for each row are equal to the rows above.  Here is my code and it is not working.
Can you please help me.
If I don't take care of the 3rd columns it was doing good.

It always give me a NExt error or else without if

Thanks in advance.

dim i as Integer  ' row 6 to end
Dim j As Integer   ' columns F to U
   
   For i = lastrow To 6 Step -1
    
        If Cells(i, 2) = Cells(i - 1, 2) And Cells(i, 6) = Cells(i - 1, 6) Then
                 
		If Cells(i - 1, 23) = Cells(i, 23) Then
    
			For j = 6 To 22 Step 1
                    		If Cells(i, j) = Cells(i - 1, j) Then
  '  			Next j
                      
                      	Cells(i - 1, 1) = Cells(i - 1, 1) + Cells(i, 1)
                      	Rows(i).Delete Shift:=xlUp
            
                Else
                    
                End If
        Else                          ' put color  'met la ligne en couleur
                Rows(i).Select
                
        	        With Selection.Interior
                	 .Pattern = xlSolid
                    	.PatternColorIndex = xlAutomatic
                    	.Color = 65535
                    	.TintAndShade = 0
                    	

Open in new window

0
Hi

I want to build a Excel VBA function that will find the cell value based on the intersection point
between a row header and a column header.

The following image shows the row header "Plant4" and the column header "Mar". If I pass these into the function
I want return the value 339.3691

What VBA code would I use in my function?
1Thanks
0
I have Titles on my PowerPoint slides so that they appear on the Outline. I don't want these Titles to appear on the presentation.

I need to programatically turn the Fill color of the text to No Fill. And back again to Black.

Can someone show me the VBA code for this please?

Thanks.
0
I have an Excel sheet with many Ledger Numbers in Col B and many Accountant Codes in Col A

All the Ledger Numbers are in the  group of the Accountant Code at the top of the group (Col A)

Instead of the Ledger Codes going down the rows I want them going across the columns.

 I want the ledger numbers in Col B appearing alongside the Accountant Code in Col E wherever a new Accountant Code starts
Rows 11, 15 and 20 show the ideal layout.

How can I achieve this without copy / paste transpose repeatedly.
Thanks
Sideways-paste-ledgers.xlsx
0
When I use the below used range it selects over 1,000,000 rows ?  Why as I only have 5 rows of data...I even manually deleted row6+ and all Columns beyond COl D and still no luck..

Worksheets("Sheet1").Activate 
ActiveSheet.UsedRange.Select

Open in new window

0
Hi

I am looking for a good website that has step by step tutorials for Excel VBA. Can anyone please suggest a website or two.

Thank you
0
I inherited a large spreadsheet that has a ton of formulas and its causing it to lag.  I’m hoping that by using VBA to replace a lot of the formulas that it will reduce the lag time when making changes or running other code.  
On the attached example spreadsheet on sheet1 column C is where I have the vlookup formula.  My goal is to run a code that will check to see if row A has data in it and if so, input the value on the vlookup in row C.  Then continue to the next row until it reaches a blank cell.
Example.xlsx
0
Exploring SharePoint 2016
LVL 12
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Hello,

I have the following script which, as you can see, does an auto fill from range G2:G8547.  How can I modify this so instead of this method, which is not efficient when more rows get added, change it to a loop?  Basically it will auto fill down the column so as long as there is content in column A.

'   DURATION COLUMN
    
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Duration Full"
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
    Range("G2").Select
    Selection.NumberFormat = "0.00"
[b]    Selection.AutoFill Destination:=Range("G2:G8547")[/b]
    Range("G2:G8547").Select
    Range("G2").Select

Open in new window


Thanks!
0
Combine cell data based on values in adjacent cells. Sample screenshot attached.

Is there a formula or VBA that can achieve this?
It's OK to have helper columns if needed.
screenshot
0
Hi,

Is there a way to limit the range of a timeline created in Excel?
The issue in my file is that the timeline date starts at 01 Jan 2017 and ends on 31 Dec 2018,
but my data cover only from Nov 2017 to march 2018.

Thanks.
order.xlsx
0
Hi experts

Hope you can help

I was kindly provided with some VBA a few months back that randomly selected a desired number of items from a list on Sheet1 in Column A and trandferred them onto Sheet2 in order to print as a list of random checks

It works perfect however due to a change of procedure, Id like to know if there is a way to modify the code to ignore anything in Column A on Sheet1 that begins with a letter (ie. randomly select those that start with a number only).

I understand I could filter out the entries however Im trying to cater to all capabilities and some users are not well versed in performing these operations

If you could provide some assistance I would be very grateful - I have attached an example file for your perusal

J
Book1.xlsm
0
Hi Experts,

I have the following code which loops thru all fields from a text data file (csv), reads the value and constructs a string.
Would like to have the following modification.
For each field containing a date/time value, check if the time is midnight like "2018-08-01 00:00", in that case should change the time to either 24:00 or 00:00 AM

For Each ColumnName In columnsName

                    fieldName = Replace(ColumnName, " ", "")

                    fieldValue = Mid(columns(c), 2, Len(columns(c)) - 2)

                    patient(fieldName) = fieldValue

                    c = c + 1
Next

objHTTP.SetTimeouts 0, 0, -1, -1
objHTTP.Send JsonConverter.ConvertToJson(patient)

Open in new window

0
Hi
I'm now coping with extract the sentences containing keywords in Excel, but failed.

For example, I have several columns in an excel. In Column C, there are original texts where we look for the key words. There are several key words (i.e. "kitten", "cat" in this example). The sentence containing one of these key words will be extracted and put into a separate column, i.e. Column D. If there are several sentences containing these words , then the sentences will be listed into separate columns (it is also OK to listed into the same column). I attached an example.

Up to now, I only figured out doing it by using one keywords with excel formulas. Is it possible to do it with VBA. Or any other good suggestion?

Thank you very much!

Best Wishes!

Yue
Example.xlsx
0

VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.