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

Dear Experts:

I would like to run a macro on the active worksheet with the following requirements:

If ALL of the rows 23 to 541 are hidden in the current worksheet, the macro has to say so in a msgbox and the macro is to exit.
If any number of the rows 23 to 541 are not hidden (it may be just one, or two etc. that are not hidden in that range) the macro is to call up a macro called 'Copy_Cells'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

I have a table called "Parameters" in Access 2010 that contains a ranges of years by Category that I'd like to retrieve

Parameters table
I have a data table that I'd like to retrieve records from on "years" that fall within the minimum and maximum year range by category on the Parameters table. For example, based upon my parameters, the data highlighted in yellow below is the data that would be retrieved.

Data
I appreciate any recommendations as to what would be the most efficient way to execute this. Ideally, I'd prefer to do this through SQL, but if this can't be achieved in a single query, I'm open to running a VBA function or whatever you think is best.

Thanks in advance for any suggestions!
0
I have a code sub that I need help with. Both the data source and the form control name are both DepartmentID. Is there an easy way to identify the data name from the form control name?

Private Sub DepartmentID_DblClick(Cancel As Integer)
On Error GoTo Err_DepartmentID_DblClick
    Dim lngDepartmentID As Long

' I believe the first DepartmentID is the table field name and the second is the form control because of the .Text=""  
 If IsNull(Me![DepartmentID]) Then
        Me![DepartmentID].Text = ""
    Else
        lngDepartmentID = Me![DepartmentID]
        Me![DepartmentID] = Null
    End If
    DoCmd.OpenForm "Departments", , , , , acDialog, "GotoNew"
    Me![DepartmentID].Requery
    If lngDepartmentID <> 0 Then Me![DepartmentID] = lngDepartmentID

Exit_DepartmentID_DblClick:
    Exit Sub

Err_DepartmentID_DblClick:
    MsgBox Err.Description
    Resume Exit_DepartmentID_DblClick
End Sub

Open in new window

0
Dear Experts:


I would like to check if the 'Wss' Worksheet's used range has data, i.e. the worksheet is blank or not ...

The macro however should only check the visible cells, because there are thousands of hidden rows which contain data.  

If all of the visible cells are blank (again, the invisible / hidden cells are not to be considered) the macro is to say so and the macro is to exit

Help is much appreciated. Thank you very much in advance.

Regards, Andreas



Sub Check_For_Blank_Cells_in_Visible_Cells_Only

Dim Wss As Worksheet
Dim i As Integer

Set Wss = ThisWorkbook.Worksheets("Customized_Choice")


End Sub
0
I need help with a function that will filter a two dimensional VBA array based on some criteria and store the filtered elements into a new array.
What I need would be clear from the code included.
As you would note I am running a loop to filter a startingArray and storing the matching elements in filteredArray.
The code I have posted works but in this case I was able to ReDim filteredArray(1 To 3, 1 To 3) As Variant because I knew how many elements I would get in the filtered array (in this case 3). In a real scenario I would not be able to do this because I would not know in advance in how many elements in my starting array would match the filtering criteria.
I would appreciate your help in the correct way of doing this.

Private Sub FilterTwoDimensionalArray()

Dim startingArray() As Variant
ReDim startingArray(1 To 6, 1 To 3)

Dim filteredArray() As Variant
ReDim filteredArray(1 To 3, 1 To 3) As Variant
' In the above line I am able to Redim filteredArray to (1 To 3, 1 To 3)
' because I know in advance how many elements filteredArray will need to accomodate
' as I can see the data


startingArray(1, 1) = 1
startingArray(1, 2) = 3
startingArray(1, 3) = "This is an apple."

startingArray(2, 1) = 4
startingArray(2, 2) = 9
startingArray(2, 3) = "He looked happy."

startingArray(3, 1) = 10
startingArray(3, 2) = 12
startingArray(3, 3) = "This is an apple."

startingArray(4, 1) = 13
startingArray(4, 2) = 16
startingArray(4, 3) = "This is a good 

Open in new window

0
This question relates to previous question...
https://www.experts-exchange.com/questions/29139086/VB-code-to-modify-records-associated-with-another.html

I tried to add a further comment that I hoped would re-open it but it does not seem to do that - sorry.

So my followup question is ......
The solution code given in the original post works fine and sets the required flag in the record.  However, I do also need to write some text into a field in the record   (CommentsNotSent) taken from a field on the form (CancellationText).

My code currently looks like this (and works fine) ....
With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                If vbYes = MsgBox("Would you like to write off this invoice ...." & vbCrLf & vbCrLf _
                & "Number - " & ![Invoice ID] & vbCrLf _
                & "Date - " & ![InvoiceDate] & vbCrLf _
                & "Type - " & ![InvoiceType] & vbCrLf _
                & "Description - " & ![Item_1_Description] & vbCrLf _
                & "Amount - £" & ![Total_Amount], vbQuestion Or vbYesNo, "Write off invoice") Then
                    .Edit
                    ![WrittenOff] = True
                    ![CommentsNotSent] =  [CancellationText]
                    .Update
                End If
                .MoveNext
            Loop
        End If
    End With

However, the problem is that the field CommentsNotSent may already contain some text and the new text needs to be …
0
Greetings experts,

I am currently trying to automate tasks between Excel and Outlook, however, there seems to be a recurring issue.

I keep getting the "user-defined-type-not-defined" error despite already having ensured that the Microsoft reference under the Tools tab has been checked.

The line that the code appears to have this error is something like this
Dim App As New Outlook.Application

Open in new window


Any help is much appreciated! :)
0
Dear Experts:

I got an .xlsm file which only should be saved as .xlsm file by the user.

Is it possible to restrict the user to just save it as .xlsm-file and not as an .xls or .xlsx or other file type? And If yes, how would this VBA code look like?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Dear Experts:

There is a built-in command in MS Excel that says:

File - Share - E-mail - as attachment (Excel 2013-2016) or
File - Save&Send - as e-mail attachment (Excel 2010)

For some specific reason I would like to intercept this built-in functionality in the following way.

Right before the user clicks the Save&Send (or Share) command I would like to run a macro code.

Is this possible?

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
0
Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F85" is included, i.e. the IF statement is true
 if all cells in that range ("F23" to "F85") have the boolean value of 'False'.

I do not want to add more than 60 'And' formulas to my vba Code. I am sure there is a quicker and shorter way.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Open in new window

0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F30" is included, i.e. the IF statement is true
 if any number of Cells (1 to 8) in the range "F23" to "F30" has the boolean value of 'False'.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Open in new window

0
Excel VBA
MS Outlook 2010
VBA runs code to grab the body of the Outlook email

Early binding does not create an issue so far...
However,  late binding triggers an Outlook Pop Up Message:OutLook Security Message programatically : A program is trying to access e-mail addresses ?
Early Binding does not appear to create an issue ?  Note I am not allowed to change my Outlook settings - The Programatical Access options are greyed out.  Thus this is not an option for me to fix the late binding issue by changing my Outlook settings or loading 3rd party software.

Shouldn't I get a security message for both late binding and early binding ?  

Also why does the message indicate I am trying to access email addresses when all I am trying to do is scrape the body of the email...Also odd is I have no issue (no security messages) within Excel VBA saving off attachments from my Outlook Folders.  Its only when trying to scrape the body of the email a security message displays.


Sub Extract_Body_Subject_From_Mails()

Dim oNS As Outlook.NameSpace
Dim oFld As Outlook.Folder
Dim oMails As Outlook.Items
Dim oMailItem As Outlook.MailItem
Dim oProp As Outlook.PropertyPage

Dim sSubject As String
Dim sBody

On Error GoTo Err_OL

Set oNS = Application.GetNamespace("MAPI")
Set oFld = oNS.GetDefaultFolder(olFolderInbox)
Set oMails = oFld.Items

For Each oMailItem In oMails
sBody = oMailItem.Body
sSubject = oMailItem.Subject 'This property corresponds to the MAPI property PR_SUBJECT. 

Open in new window

0
calculate days and time between two steps; in the attached file i need to concatenate  number and name and calculate difference between each step; if the step is repeating consider the latest or last updated one
0
If I have an Access 2016 table that has the following layout

PKID | Month1 | Month2 | Month3 | Month4 | Month5 |etc...
___________________________________________________________
1       |     100     |                |     70       |                |                |
___________________________________________________________
2       |     28       |        3      |                |     25       |                |
___________________________________________________________
3       |                |                |                |                |       8        |
___________________________________________________________


Is there a way to write a query or code that outputs to a table to transpose the values along with the field name into something that looks like this:

PKID |  Month  | Value     |
___________________________
1       | Month1 |    100      |
____________________________
1       | Month3 |     70       |
____________________________
2       | Month1 |    28        |
____________________________
2       | Month2 |    3           |
____________________________
2       | Month4 |    25         |
____________________________
3       | Month5 |     8         |
____________________________

It doesn't have to be a query.  Whatever is the best solution, works for me...
0
Hello,

I am looking for a procedure in order to  
Open file attached located at: Application.StartupPath
If the file doesn’t exist exit sub with the following message “File: File.name doesn’t exist”
Else continue and transfer sheet “1-“ to active workbook and rename it with current date: YYYYMMDDMMSS. Continue procedure with the following message “Do you want to remove the various sheets except sheet already transferred. If Yes remove them else keep them.

If you have questions, please contact me.
1-Table-template.xlsx
0
I have the following VB command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_testquery", "strFilePathName", True

Is there a way if "q_testquery" returns no records still have an excel document generated with "No Records Found"?
0
I'm using the following code to download files from the internet and save to a location on my computer. I need to close the current browser window after the download (not the application). Thanks


Dim myURL As String

myURL = "https://portal.kkk.kkknet/wps/portal/unauthportal/home/"


Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "zzzz", "zzzz"
HttpReq.send

ShellEx "https://portal.kkk.kkknet/ibmcognos/bi/?pathRef=.public_folders%2FMAS%2FALJ+HQ+Ad+Hoc+Reports%2FPerformance+Goals%FFY19+Aged+Case+Goal+List&format=spreadsheetML&Download=true&prompt=true"


myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile CurrentProject.Path & "\" & "FY19 Aged Case Goal List.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close


    
End If

Open in new window

0
Hi, can any show me how to apply the following vba syntax to open another secured Access DB2 from DB1's Form Button click?
Both DB are running Access 2003 Runtime and configured with the same workgroup mdw, so that users do not need to retype their user name and pw again when DB2 is opened.

Desktop Icon Click is "C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\Server\DBFiles\DB2.mde" /wrkgrp "\\Server\DBFiles\Security.mdw" /runtime

DB1FormButton Click()
Dim strMdb As String
 strMdb = "\\Server\DBFiles\DB2.mde "
 Dim appSecondInstance As New Access.Application
 With appSecondInstance
    .Visible = True
    .UserControl = True
    .OpenCurrentDatabase strMdb
    .DoCmd.OpenForm "Applicant Details", acNormal
    .Forms("ClientArchive").Filter = "ClientID Like '*" & Me.Searchinfo & "*'"  
    .Forms("ClientArchive").FilterOn = True
   
 End With
0
Hi Gurus,

Is there a way i can timers set in excel
for eg :

1.  i will have two columns ie start date and end date , i want in third column to tell me with the current date on how many days left
2.so if i reach threshold lets say ' three days left' the cell should change color to red or any other color .

Reason is to monitor the tasks assigned to my colleagues and to follow up priorities based on nearest deadline dates


Thanks,
Sid
0
Starting with Angular 5
LVL 13
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

In my excel spreadsheet 2016 I got this Visual Basic Error when I am trying to pass  date parameters to my store procedure.
Not sure why this is happening.


Run Time Error ‘9’
Subscript out of Range
Private Sub CommandButton1_Click()

Dim SellStartDate As Date  'Declare the SellStartDate as Date
Dim SellEndDate As Date    'Declare the SellEndDate as Date

SellStartDate = Sheets("Sheet1").Range("B3").Value   'Pass value from cell B3 to SellStartDate variable
SellEndDate = Sheets("Sheet1").Range("B4").Value     'Pass value from cell B4 to SellEndDate variable


With ActiveWorkbook.Connections("SP_Connection2")<<<<<< Error line here - solved
 .OLEDBConnection.CommandText = "EXEC Datawarehouse.dbo.sp_Test_Data '" & SellStartDate & "','" & SellEndDate & "'"
 .Refresh <<<< Run Time Error '1004'
End With

End Sub
I solved it it was wrong speling for : "SP_Connction2" vs "SP_Connection_2".
Now I passed it and got  this error :
Run-Time error '1004'
Exception has been thrown by target of an invocation
1
I have a worksheet where the contents of certain cells are formatted with red text.  I am curious through VBA if there is a way to delete any cell's contents across all sheets in a workbook where there is red text.  Example attached.
0
I have a search form that looks for the first instance of a record, such as last name and opens the corresponding form, employee.

But what if there are more than one person with the same last name, that is when I would like for the next and previous record button to become available.

I know there is a way of doing this,
Something along these lines, just not sure of the syntax.

If Record count > 1 then
me.previous.enabled = true
me.next.enabled = true

John

End If
0
I need to separate the data for each school (Column B) listed in the attached workbook into separate sheets.  I will need to run the code to refresh the sheets when the data source is changed and have put together some vba code.  This will work as long as the school name is less than 31 characters.  I then included a LEFT function to extract only the first 31 characters - this works the first time I run the code, but not in reruns.  Also, the workbook is running really slowly.  Could anyone advise what I can do to to fix this?
Offers-And-Acceptances.xlsm
0
Hi ,
i would like to know for my attached excel , is there a way i can make it menu based .

1 . Serial Number i would like to go in sequence starts from 1
2. Brand , i should select from pull down ( not in excel ) but in menu format
3. Multi type selection i need to get it from menu .

if you can give me ideas, it would be great for me start of with the project .

Cheers,
Sid
Test1-Menu-VBA.xlsx
0
I would like to add a message box or a pop up that is displayed while the record is updating and once complete it closes the message and the window.

This is what I have in the Form_BeforeUpdate event:

If Me.NewRecord Then
        Call AuditChanges("Employee_ID", "NEW")
Else
        Call AuditChanges("Employee_ID", "EDIT")
End If


I wouldlike a MsgBox to display that goes away once the changes are recorded.

Users are not liking that the screen tells them nothing of what is going on while they wait for it to close.

Thanks,
John
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.