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

Hi,

I encapsulated a form in a class, with the goal to catch some of the form's event, but so far, no events are caught.

EventCatcher class:
Option Explicit

Private WithEvents mParent As Access.Form

Private Sub Class_Initialize()
    Set mParent = forms("Form_1")
    mParent.OnGotFocus = "[Event Procedure]"
End Sub

Private Sub mParent_GotFocus()
    Debug.Print "event GotFocus"
End Sub

Open in new window

Any clue about what's going on ?

Note: I'm able to catch control's events without problem,
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Excel 2010
windows 10

What I  have:
I have a workbook with several sheets.
I need the information in ColumnA  (with a Column Name)

What I need:
I need to take the data from Column A(from all sheets) and put it in a text file.

Thanks
fordraiders
0
Is there a way to retain the color scheme of a pivot chart when filtering it?  I have reset the color scheme for a bar chart only to have it revert back to its original colors after filtering it.
How can I retain the colors in the pivot chart after filtering it?
0
Looking for recommendations regarding encrypting and decrypting files at run-time, using VBA.

I have a client who has a bunch (thousands) of files on her network share which contain unencrypted PII.  I'm looking for some vba code which I could use to encrypt these files when they are loaded into her application and then decrypt them when a user needs to view the file.

If there is another option (password protecting a zipped folder) that can be implemented via VBA, that would be acceptable as well.

Dale
0
I'm using a customised validation rule in access. Whenever an error occurs  I call the Forms FormError event and handle the error message according to the language of the user. For example if a variable is declared as numeric and the user enters characters, access will automatically call the formError event. However, I have recently changed to the new 64bit version and here the error is handled without calling the FormError event and no message at all is displayed. Is this correct or is there anything I can do to have the same behaviour as in the 32bit version? Thank you so much for any help!
0
Hi,

Basically, I need to recreate 3 sheets on excel workbook using access vba. The 4th sheet on excel has got table/chart which have got excel formulas and they are looking into the other 3 sheets to generate values. Each day when I delete these 3 sheets, and recreate them, the formulas in the 4th sheet get corrupted. Even if I generate other 3 sheets, the values on the 4th sheet are not being updated because of the corrupted formulas. what is a solution for this? One solution is to generate formulas on 4th excel sheet using access vba. But there are lots of formulas (more than 100 as of now, big chance to increase). Is there any other suggestion like keeping a sheet with formulas and copy paste them using access vba.?But if I copy paste I need to do "replace" sheet name at the end and its going to be complicated. Any help on this greatly appreciated.

thank you
A
0
Hello experts,

I have multiple vba procedures which I would like to share with final users so they can re-use it when they open excel application. I was wondering what is the best way to automatically share with them those procedures. I was thinking to add all those procedures into an add-in file and then share the file however I don’t know if it is the best way.

Thank you very much for your feedback.
0
I am trying to copy a formula in a range of cells. I placed the worksheet activate and nothing is happening. What am I missing.


Private Sub Worksheet_Activate()
Range("B6").Select

Range("B10:B1000").Formula = "=IFERROR(MID(C17,FIND(" * ",SUBSTITUTE(C17," \ "," * ",LEN(C17)-LEN(SUBSTITUTE(C17," \ ",""))))+1,LEN(C17)),"")"
Range("D10:D1000").Formula = "=LEFT(B10,10)"

End Sub
0
Hi Experts,

Hoping someone can help me with a solution to automate a manual daily task
I need top download a file from a http (url - report server) site and save it to a folder at regular intervals ( hourly )

The manual process is as below
1) Go to the website/url using IE
2) Click on a drop-down item and select "Export to Excel" (Actions --> Export (option 3) --> Excel (option 5) --> Its a sub-menu see html below
3) A pop-up appears to Open or Save the .xlsx file
4) Click on Save button
5) Save  it to a folder location on my pc.

I cant use any 3rd party tools or software as I cant download it due to restrictions in my organisation.

Preferably excel/vba solution or if there is an inbuilt windows solution to capture and record actions above and then schedule it at regular intervals.

Thanks


HTML:


            <td class="ms-toolbar sqlrv-ToolBarCell" nowrap="nowrap">
      <div id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu">
                              <span style="display:none"><menu type='ServerMenu' id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ctl00"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ReportBuilder_forRendering" type="option" onMenuClick="STSNavigate2(event,'http://project/PWA/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderViewer&ReportPath=http%3a%2f%2fproject%2fPWA%2fReports%2fPSD+Extract.rdl');" text="Open with Report Builder" menuGroupId="2147483647"></ie:menuitem>
                              …
0
Im new to VBA and got some problems with making a index page from values in my msgbox.

dont under stand why this 2 lines of codes dosent work.
Set wsName = Me.BDClass.Value

Open in new window

and this one
shIndex.Range("B5").End(xlDown).Offset(1, 0).Select 

Open in new window


Private Sub AddFromBtn_Click()

    Dim r As Range
    
    'Checks if BDClass allready exist
    If BDClass.Value <> "" Then
        Set r = wsIndex.Range("B:B").CurrentRegion.Find(BDClass.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en klassifikation med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives klassifikation")
            Exit Sub
    End If
    
    'Checks if BDName allready exist
    If BDName.Value <> "" Then
        Set r = wsIndex.Range("C5").CurrentRegion.Find(BDName.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en bygningsdel med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives bygningsdel navn")
            Exit Sub
    End If
    
    
    'Creates new sheet
    Worksheets("Temp").Copy After:=Worksheets("Index") 'copy Temp to after Index
        ActiveSheet.Name = BDClass.Value 'Renames the sheet to BDClass value
        
    
    'Writes value to new sheet
    ActiveSheet.Range("C3") = BDClass.Value
    ActiveSheet.Range("E3") = BDName.Value
    
    
    'Writes value to Index 

Open in new window

0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I a am Civil Engineer and I am using VBA for Excel from last 10 years. Recently I developed a macro to generate payslips of employees and my friend's company purchased it.

I think more companies require that tool which can make their life easier and I am also interested in the monetary benefit.

But a person with a little knowledge in VBA can break the password, will be able to steal the code and I don't want that to happen. I am looking for ways to make the code secured.

In this regard I expect expert's help.

Thanks in advance
Ajay
0
Hello experts,
I have the following report attached.
I am looking for a procedure to remove blue lines.
Procedure should:
1-Remove Output sheet if it exists
2-Copy Input Sheet
3-Rename copied Sheet with Output name
Remove the various blue lines. They have in common the following attributes: Values of C column=As Code, values of D column=Resource Organization, values of J column=Projects, Values of K column Project ID
4-Add headers based on blue lines removed in topic 3

 

I attached example file.
If you have questions, please contact me.
Remove-lines-based-on-values.xlsm
0
I need to include a web browser control on a form in MS Outlook but I am getting a strange variance when I run the code from the VBA editor versus a shortcut on my Quick Access Toolbar.

I have added the control to the form and created a module that displays it. The relevant part of the code is as follows:
    frmManualAdd.WebBrowser1.Navigate2 "http://www.google.com"
    frmManualAdd.Show

When I run the code by pressing the Run (or F5) button from the VBA editor, the form correctly loads and displays by browser control. However when I run the same module from a shortcut I have added to my Quick Access Toolbar, the form displays without the Web Browser. What can I do to get it to work?
0
I have a workbook with 213 tabs. I have a macro that activates each sheet and then runs an existing web query that has been tailored to that particular sheet. What I need to do is to edit the web query for all 213 tabs and I'm hoping that I can do it with VBA.

For example I need to change this:
     https://xyz.com/vianet/ami/routing.asp?DIR=Y&D=7&AC=001
to this:
     https://xyz.com/vianet/ami/routing.asp?DIR=Y&D=21&AC=001

The queries are all the same except for the last 3 characters, which happen to be the name of that tab. So the VBA just needs to replace the 7 with a 21 and the last 3 characters to match the sheet name.

Is there a way to do that with VBA? I'm under a pretty tight deadline, as in by the end of the day Thursday, September 20th.

Thanks!
John
0
Hi,

I have an issue with a chart not updating.
The structure is:
A main form (frmMain)
A subform in the main form (subfrmAge) - It contains a crosstab query (XAge)
A chart in a subform (subChartAge) - The record source is (XAge)

The problem is that when I filter XAge with a button on my main form, the values from subChartAge is updated, however the chart isn't.

I've tried adding
        
    Me.Requery
    Me.Refresh
    Me.ChartAge.Requery
    Me.ChartAge.Requery

Open in new window

However, the chart is not updating with the query. Why is this happening?
ChartNotUpdating.png
0
I need to create one query to run every day , using Microsoft Access that finds records from the previous work day. Tues to Friday, the previous work day is date()- 1. Simple enough.  But on Monday, if I use date()-1, it will miss records from that previous Friday or Saturday.   its an iif statement  but I need help with the rest please.
0
Excel has data in columns A and B. Either columns can have blank and duplicates.

What is the fastest code that can find the number of unique matches between them?
0
I am sorting the status of a job as Loaded / Unloaded based on the text within the cell. What i need is to have a macro button installed which when pressed should read the cell value in  the status column and if it finds "Loaded" in column "S" then move the entire row to a different workbook (lets call it workbook2). Upon the transfer of the row of data it will delete from the current sheet. Workbook2 will have  two sheets for the sorted data - one for loaded and one for unloaded. An important feature of this code is that workbook2 already exists and therefore this macro will just need to open this notebook and save / close once the rows have been transferred.

My intent is to use this macro command button as an archive button to export the data from the current file to the next available row in  workbook2 (a pre-existing workbook).
1
I'm using the Access code below to copy files from (Outlook) OMHA.HQ.MI.REPORTS Inbox to a subfolder (MATS Backup folder) under the Inbox. I'm getting an error -Runtime error 450 wrong number of arguments or invalid property on line: Item.Copy myDestFolder. Any ideals?

 Dim myDestFolder As Outlook.Folder
 Dim ns As NameSpace
 Dim Inbox As Outlook.MAPIFolder
 Dim Item As Object
 Dim SubFolder As MAPIFolder
   
 
 
 Set ns = GetNamespace("MAPI")

 Set recip = ns.CreateRecipient("OMHA.HQ.MI.REPORTS")
   
 Set Inbox = ns.GetSharedDefaultFolder(recip, olFolderInbox)
    
 Set SubFolder = Inbox.Folders("MATS Backup")
 
 Set Items = Inbox.Items
 Set myDestFolder = SubFolder
 Set Item = Items.Find("[Subject] = 'MATS Download'")
 While TypeName(Item) <> "Nothing"
 Pause (0.5)
 Item.Copy myDestFolder
 Set Item = Items.FindNext
 Pause (0.5)
 Wend

Open in new window

0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hello,

I have the following procedure which allows me to replace values based on config sheet.
I would like to add the procedure to my personal.xlsb with the following requirements:

1-Add msgbox (yes, no) which says “Have you already created config sheet?” vbCtrl
“Sheet Name: Config-Replacement” vbCtrl
“Column A values to replace” vbCtrl
“Column B revised values” vbCtrl
“Column C reference column in which are located values to replace” vbCtrl
“Column D sheet name in which replacement procedure should be applied" vbCtrl
“Column E flag value: 1 for exact values” vbCtrl

2-If vbno is activated the config sheet reported in my example should be created with format and
headers. Else Run the replacement procedure
3-If vbno is activated and config sheet already exist. Exit sub and display msgbox which says “Config sheet already exist."


If you have questions, please contact me.
Replace-values-based-on-config-shee.xlsm
0
Hi Experts,

Hoping someone can help me with a solution to automate a manual daily task
I need top download a file from a http (url - report server) site and save it to a folder at regular intervals ( hourly )

The manual process is as below
1) Go to the website/url using IE
2) Click on a drop-down item and select "Export to Excel"
3) A pop-up appears to Open or Save the .xlsx file
4) Click on Save button
5) Save  it to a folder location on my pc.

I cant use any 3rd party tools or software as I cant download it due to restrictions in my organisation.

Preferably excel/vba solution or if there is an inbuilt windows solution to capture and record actions above and then schedule it at regular intervals.

Thanks


HTML:


            <td class="ms-toolbar sqlrv-ToolBarCell" nowrap="nowrap">
      <div id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu">
                              <span style="display:none"><menu type='ServerMenu' id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ctl00"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ReportBuilder_forRendering" type="option" onMenuClick="STSNavigate2(event,'http://project/PWA/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderViewer&ReportPath=http%3a%2f%2fproject%2fPWA%2fReports%2fPSD+Extract.rdl');" text="Open with Report Builder" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem …
0
I need to link to a SharePoint list from within Access and only retrieve the records for a particular view of the list.
It is not practical to pull down the entire list and then filter/SQL search within Access.
I want Access to only see the records in a particular view and only those columns in that view.

This is all working using Excel with a linked table and refresh all to update it from SharePoint I'd like to avoid the users
having to open a workbook at all  - just open access and the data is there (perhaps asking them if they want to refresh the data from SP)

I know to how directly link to a SharePoint list from Access - but that tries to bring down all records and all fields
(over 2000 records and 240 fields)  - not practical for end users.

I'm experienced in VBA, SharePoint, .. etc so a VBA solution is fine.
Environment:
Excel/Access 2016, SharePoint 2016, full control on the site w tons of workflows, JS, etc..
No infopath, No Access web apps, must work in FF and IE

so to summarize -
How to establish a link to a particular view of a SP list in access  - including refreshing etc
Specific examples, VBA code, ... sought
thanks
0
Some time back we placed a question on how to read data from an internet page and save the specific content to an excel workbook (maybe it was a VBA).  We tried to search it in EE but can't find it.  So hope we can get the answer again.  We have certain web pages that we need get a specific data and save it's contents to an Excel.  For example, a user logs into a specific web page and register his work on it.  If a non-user views it, it always present the last user who saved his entry.  We want to read that user name and save it in the excel workbook.  The user name will always be in the specific location.  What is the best and automated manner in pulling data from a website into Excel?
0
I have a form “frmObrasAlbum” where I have one combo, one textbox and a subform “frmInterpretesSub”.

I need to trigger a unique event in the subform when I leave the parent form (and entre in the child form), so the child form is automatically filled by a vba procedure (then I can edit the default values or accept those generated by the code).

My problem is, I cannot use an event of one of the controls of the child form because when I edit the default value of that control the vba code is triggered again (because it is linked to that control).

I tried to use “form” events, like on enter/set focus/got focus, but I does not fire the vba procedure.

Can someone give me an advice of which way I should go?
0
Hello experts,

I would like to create a procedure to create multiple folders based on path reported in column A.
If folder cannot be created skip cell and continue to next one.

I have initiate the first part.
Could someone help me to finish the rest of the procedure and add the error conditions?
I attached example file.

Thank you very much for your help.
Create-folders-based-on-column.xlsm
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.