VBA

6K

Solutions

22

Articles & Videos

2K

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

Respected All,

I have Recorded a Macro to save a file  Please kindly Guide me i want name would be dynamic as if File Name is "CPR as at June 15, 2017-GRW" then it can save with concatenation of today Date 24-06-2017 eg. "CPR as at June 15, 2017-GRW-24-06-2017".

Please help and advice.

Regards Farhan

Sub Macro1()
'
' Macro1 Macro
'

'
    ChDir "C:\Users\Khalid Mahmood\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Khalid Mahmood\Desktop\CPR as at June 15, 2017-GRW-24.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
End Sub
0
[Webinar] Learn How Hackers Steal Your Credentials
LVL 8
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Hello,

I have a database (attached) that create worksheets based off the amount of work items in column B of the "summary" datasheet. The macro works great, except for that if the user clicks the button again after running the macro and runs the macro a second time, a new set of worksheets are created with the same name except with the time of the rerun also in the name. I was wondering if it would be possible to make it so that when the button is clicked a second time and the user chooses to create worksheets again that the worksheets are just updated? For example if a new row was added after creating worksheets and the macro was run again then just that new row would show up and all the other worksheets would just stay the same. Please let me know if I need to explain myself more clearly!

Thanks!

here is the code:

Sub CreateWorkItems()
    Dim WS As Worksheet
    Dim WSItem As Worksheet
    Dim WSTemp As Worksheet
    Dim MaxRow As Long, I As Long
    Dim sClientId As String, sJobId As String, sWItem As String

    '---> Disable Events
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False


        '---> Set Variables
        Set WS = ActiveSheet
        MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
        Set WSTemp = Sheets("template")
        sClientId = WS.Range("B1")
        sJobId = WS.Range("B2")

        If MaxRow >= 9 Then
            For I = 9 To MaxRow
        …
0
Hello!

I have a database (attached) and I want to be able to export the first two sheets to a PDF. However, the code I have now (below) which runs after clicking the Print Bid button, only converts part of the first worksheet. When the user is using this database there could potentially be many more work items and therefore I need the code to convert everything in all the columns to a PDF, except column C. That is the next problem, the Jump To column, column C, in the first worksheet should not be visible in the PDF but right now, despite the code, it is. If anyone has any suggestions on how to change these two things that would be wonderful! Thank you!

Sub PrintToPDF()
    Dim oWs As Worksheet
    Sheets("summary").Columns("C:C").EntireColumn.Hidden = True
    Set oWs = Sheets("summary")
    With oWs.PageSetup
        .FitToPagesWide = 1    ''/// prints to one page
    End With

    oWs.Columns("C:C").EntireColumn.Hidden = False

    With Sheets("service rates").PageSetup
        .FitToPagesWide = 1    ''/// prints to one page
    End With
    ''///Save the Array of worksheets (which will be selected) as a PDF
    ThisWorkbook.Sheets(Array("summary", "service rates")).Select
    On Error Resume Next
    oWs.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="Test.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            …
0
I have this solution
need to improve with vba the count

add an countig to the column A in count sheet

num3.png

also Need add
blank column in sheet PAIR  between CD, DE
num3a.pngnum3data.xlsm
0
Hello,

This question is in follow-up to another recent thread located here:

Modify formatting of selected intracellular text

In that thread, a VBA solution was provided to alter the formatting of certain portions of an Excel text entry while leaving the remaining portions unchanged. That solution (as far as I can determine with my limited knowledge of VBA) operates by including, within the code, the literal strings of text (ie the specific words) which are to be reformatted.

The follow-up question now is:

Can the VBA code described above be modified so that in place of the strings of text (which need to be reformatted), it contains variables which obtain those strings from say, an adjacent column in the spreadsheet?

I'm attaching a file that illustrates two possible options for the functionality I've got in mind. The first option (in the sheet tab labeled Text_Strings) is shown in the first screenshot below. The second option (in the sheet tab labeled MID_Function) is shown in the second screenshot below.

Note: I believe the second option (MID_Function), if it is doable, is a much better way to go than the first since it allows for a more precise designation of the text strings to be reformatted and also eliminates potential problems due to duplicate or repeated strings.

Option #1 Text_Strings
0
Hello!

I have created a button that I want to allow the user to be able to print just certain sheets of the workbook. I am wanting to create a macro for the button that prints columns A-J, but NOT column C, from the worksheet "summary". Then I would also like the worksheet called "service rates" to print right after the "summary" sheet. It would also be great if both sheets fit to the page so that they aren't being cut off!

Thanks so much :)
0
i have this solution but need to improve with vba
to count the triples from 4 numbers
num4triple.xlsm
0
I have been attempting to use this Macro that I found through VBA Express.  The Macro successfully disables the ability to right-click cut/copy/paste but the keyboard shortcuts are still active.  Any help is greatly appreciated.  I have very little knowledge about writing Macros and usually go to Google to find already existing codes to use.  Please see below:

'*** In a standard module ***
Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     …
0
I had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.
CountItemsv2.xlsm
0
Dear macro experts, please help.
I would like the macro to adjust (shrink or increase) row height anywhere in sheet depends of the text in cells. The rows and columns have Merged Cells and Wrapped Text. The text is always in other two rows and in same 11 column from B to L. Column length is static. That two rows need to be same size and the text need to fit.
Adjust.xlsx
0
On Demand Webinar - Networking for the Cloud Era
LVL 8
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Experts:

I have come across a wonderful (ready-to-use) Excel sample file that allows multiple values to be selected (in single cell) via a drop-down box.   I found the example at:  https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

I would like to modify the code provided by Sumit Bansal.   Specifically, I need multiple drop-down (i.e., for every record in an XLS, I need the drop-down for assigning POCs).

Sumit's code below allows to use the drop-down for cell C2 only though (line: "If Target.Address = "$C$2" Then").   For testing purposes, I tried modifying the code with only two cells.   The proposed VBA "If Target.Address = "$C$2" Or "$C$3" Then", however, does not work.

My question:   If I wanted the drop-down to work for, e.g., 50 rows, how can I change the code?   That is, "If Target.Address = "$C$2:$C$51" Then" does **not** work.

Thank you in advance.
EEH





Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue 

Open in new window

0
I have a file with over 5000 records I need added to a table of mine in access. Well one of my fields is call SKU. The SKU field is required and is Unique. I use a Function called UniqueString for when I insert new records one at a time. But I need to use this function to generate 5000+ Strings at a single time so I can copy and paste the row into my excel file before importing into Access?

How can this be accomplished? Thanks for the help and here is UniqueString:
Option Compare Database
Option Explicit

Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
    
    Do
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
            
        Case Is > parmLen
            'use remaining byte values as increment, mod 256
            

Open in new window

0
I would like to add another condition to the current formula in Column C, which is based on the entry in Col B. The current formula is:

=IFERROR(IF($B2="","",IF(RIGHT($B2,3)="YES","",TEXT(TODAY(),"dd mmm yyyy"))),"")

The format for Col B is always:
AA-DD MMM YYYY-NO
or
AA-DD MMM YYYY-YES

I would like to add the following condition to the above formula, based on the entry in Col E:

If Col E = "Cancelled" OR "Deferred", then populate Col C with date from Col E (formatted as above — TEXT(TODAY(),"dd mmm yyyy")

The format for Col E is always:
Cancelled-DD MMM YYYY
or
Deferred-DD MMM YYYY

Lastly, if both Col B and Col E have entries, then I would like the date from Col E to be entered in Col C, not the date from Col B.

I have attached a sample spreadsheet to show the results I am hoping for.

Thanks!
Andrea
EE_Sample_TwoConditions.xlsx
0
I am having a problem with some VBA code when it runs within a DAO Transaction. The code works fine when it is NOT running within a DAO transaction. Here are the specifics of the problem:
I modify a QueryDef.SQL property, and then I use that QueryDef in a DoCmd.TransferSpreadsheet acExport command. When that command is run I get the run-time error "3066: Query must have at least one destination field." In debug mode I can see that the SQL for the QueryDef is set to the value that I set it to in the previous step. It's a valid SELECT statement, and again, it normally works just fine when I remove the DAO transaction surrounding the code. Interestingly, when I open the query object in Access it is blank. Another interesting fact is that while the debugger is waiting at the DoCmd.TransferSpreadsheet command I can use the Immediate window to commit the transaction, and then when I resume the execution of the code it will work just fine.

So, it appears that the SQL in a QueryDef cannot be modified during a DAO transaction, but does anyone know why?
0
Hello,

I have a macro that allows for new sheets to be created based off the amount of rows in the "Work Item" column in the posted excel database. Right now the "work item" updates into the worksheets created from the "template" spreadsheet, but the "description of work item" does not update into the "description job"  column in the templates, and I would like it to. Could someone please help me with this? Thanks!
0
Thanks to Shums solution to my last question, I have now moved forward to the final question. The attached worksheet has a routine to add a new line on the first line where there is no value in column B, after B15. This is done with the Add a Line button. There is also an unused button now titled "Compound the Interest."

This routine needs to add a line using the same date in column B as the previous line AND copy the contents of the previous line columns C, F, G, H, I, J and K to the new line AND copy the amount of the previous line's column I to the new lines Column D and Column J.
Bill_Golden_NOTE-PAYABLE_v3.xlsm
0
I have the following piece of code that is getting the EntryID of an Outlook message that is selected. However, I want to have a button to execute this script on the custom toolbar of the message itself,

How I get the Entry ID of the currently "open" message ID versus a highlighted one?

Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
    
    For Each currentItem In Selection
        If currentItem.Class = olMail Then
            Set currentMail = currentItem
        End If
    Next
        
    strEntryID = currentMail.entryID

Open in new window

0
I have a frame which contains quite a lot of detail, and which has a vertical scrollbar. I want to set the scroll position of the frame, so that the most important content is visible to the user (at the bottom of the frame's scroll-able content), before the user scrolls away to other details in the frame.

I use vba.

I'd settle for presetting the slider to the bottom or top of the scollbar, but would be interested to know if I can pre-position it to any vertical position.

Thanks
Kelvin
0
In L24, I have a total value for a horse's total number of starts in harness races. In G26, H26, I26, J26 and K26 I have in each cell a value that together add up to the total value in L24.

For example:
L24: 75
G26: 10
H26: 5
I26: 30
J26: 25
K26: 5

I want that in G26:K26, there is also displayed a percentage value within parenthesis after the integer value, and this percentage value is a calculation of the cell's integer value divided with the total value in L24.

In L24, I want that there is also displayed an integer value within parenthesis after the first integer value (in this case 75), as well as a percentage value within parenthesis (this percentage value is a calculation of (G26+H26)/L24).

So in my example, the display would be this:

L24: 75 (15) (20 %)
G26: 10 (13 %)
H26: 5 (7 %)
I26: 30 (40 %)
J26: 25 (33 %)
K26: 5 (7 %)

Is this possible to achieve with the concatenate function (or &), or by using a textbox?

For the moment I will fill in the values manually in these cells, but later I will have each value retrieved/calculated from other worksheets.
0
[Webinar] How Hackers Steal Your Credentials
LVL 8
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I want users to be able to add a line in the attached worksheet. Currently, there is a crude macro (Ctrl Shift L) that adds the line. In order for the macro to work, you must be on the first blank like in Column A.

I want the macro to automatically find the first cell below B15 that is blank (in the of the attached worksheet, B46) and automatically copy C45 to C46, F45 to F46, G45 to G46, H45 to H46, I45 to I46 and K45 to K46 and the cursor ends up, once again in the case of the attached worksheet, in that first blank cell in Column B, B46.
NOTE-PAYABLE--BB--Inc.-to-AA--LLC-R.xlsm
0
Hello,

I am wondering if there is a way to set a column of a spreadsheet to not show when printing. The column is in the interior of the spreadsheet and I don't want the user to have to manually select a range for printing that does not include this column. Is there some VBA code that could work for this? It's column C on a spreadsheet called summary.

Thanks so much!
0
Hello,

How do you modify Excel VBA macro code (containing a keyboard timestamp shortcut) so that, each time the macro is invoked, it reproduces the actual shortcut keystrokes (and therefore the current time) rather than the "absolute" time value the shortcut produced when the macro was originally recorded?

After clicking to Use Relative References (ie Developer > Use Relative References), the Excel macro recorder was used and created the following code:

Sub Macro12()
'
' Macro12 Macro
'

'
    Application.Goto Reference:="DateEnd"
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "11:59:00 AM"
    ActiveCell.Offset(0, 3).Range("A1").Select
End Sub

Open in new window

The relative actions (keystrokes) appear to have been recorded properly except for the following row:
       
ActiveCell.FormulaR1C1 = "11:59:00 AM"

Open in new window

At this particular part of the recording, the shortcut [Ctrl+Shift+;] was used to enter the current time. As is shown above, the time when the macro was being recorded was apparently 11:59 AM. However now, each time the macro is run, it does not enter the current time as it should but enters 11:59 AM.

So how must that row of code be modified so that each time the macro is run in the future, it will reproduce the keystrokes shortcut and post the current time then instead of always entering 11:59 AM?

By the way, I …
0
Hi Experts!

I did search here but didn't find this. I have two two-dimensional arrays (extracted from tables in Word docs), ARRAY1(0 to 100) and ARRAY2(0 to 300). Can I create an ARRAY3(0 to 300?) that only has those elements in ARRAY2 which are not in ARRAY1?

Thanks!
0
Last I've read, there is no formal security in MS Access, but if you want to expose objects to only certain users, is it acceptable to use something like the code below to determine what objects users see...purely from a workflow perspective?
Dim CurrentWorkbenchUser As String
CurrentWorkbenchUser = Environ("USERDOMAIN") & "\" & Environ("USERNAME")
If CurrentWorkbenchUser = (name users) Then (make object visible)

Open in new window

0
Guys, This must be very simple but!!!!!!! My piece of code loops through a query I have and what it should do is work out how many records then loop through every record. What it is doing is looping through fine but missing out the last record.

Private Sub cmdEmail_Click()

On Error Resume Next
    Dim rs As DAO.Recordset
    Dim strpath As String
    Dim stDocName As String
    Dim mypath As String
    Dim sExistingReportName As String
    Dim sAttachmentName As String
    strpath = Forms![Main_Menu_frm]![temppdfloc]
    Debug.Print mypath
    'recordno = Me.Recordset.RecordCount + 1
   

DoCmd.GoToRecord Record:=acFirst
While Me.CurrentRecord < Me.Recordset.RecordCount
 
   
    sExistingReportName = "Bacs_report_All"    'Name of the Access report Object to send
    sAttachmentName = Me![PAYEE_NAME] & " Bacs Remittance"    'Name to be used for the attachment in the e-mail
    mypath = strpath & sAttachmentName & ".pdf" '"Bacs.pdf"


DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
Reports(sExistingReportName).Caption = sAttachmentName
DoCmd.OutputTo acReport, sExistingReportName, acFormatPDF, mypath, False

    Dim objMessage As Object
Set objMessage = CreateObject("CDO.Message")
 objMessage.AddAttachment mypath
 objMessage.Subject = "Bacs Remittance for " & Me![PAYEE_NAME]
 objMessage.From = "purchaseledger@altrad.com"
 objMessage.To = Me![SU_EMAIL]
 objMessage.TextBody = Me![PAYEE_NAME] & ":" & _
      vbNewLine & …
0

VBA

6K

Solutions

22

Articles & Videos

2K

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.