VBA

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

Have this data in P4 sheet (output sheet, after selected data )
exact macth

but need crate the P4_VER sheet
so i can select the data needed
after ouput is show in P4

then
SHOW Column  d, E, F, G, H ,I, J,K,L,M,N,O,p.q.s.
13 set
show selectED in yellow BACKGROUND
of the selectes DATA as needed
29050744--1-_A_n4KKK.xlsm
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have the below code that works fine, however i am trying to amend th code to no avail so that it saves the files with the leading Zeros.

the number element are store numbers and it ranges from 1 - 168

ideally if possible can you advise how do i change the code so it saves the output files like the below example if a store number is 2 digits and the 3 digits etc.

0001
0010
0120

Sub GenerateOutput()

    Dim i As Long
    Dim iGradeRow As Long
    Dim iGradeCol As Long
    Dim iPosSeqRow As Long

    Dim s(1 To 7) As String

    Dim aGradeData() As Variant
    Dim aPosSeq() As Variant

    Dim aOutput(1 To 500000, 1 To 12) As Variant
    Dim iNextOutputRow As Long

    Dim ExportWorkbook As Workbook

    Dim Site As String
    Dim Department As String
    Dim Category As String
    Dim ArticleGrade As String
    Dim dp As String
    Dim ct As String
    Dim posQty As Long
    Dim y As Long
    Dim lrStores As Long
    Dim recordId As Long
    Dim selId As Long

    '------------------------

    Application.ScreenUpdating = False

    ' Get arrays of data to loop round
    With ws_Grades
        aGradeData = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value2
    End With
    With ws_PosSeq
        aPosSeq = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 20).Value2
    End With

    s(1) = "( "
    's(2) = iGradeRow - 3
    s(3) = " / "
    's(4) = UBound(aGradeData, 1) - 3
    s(5) 

Open in new window

0
I have this VBA code: -
    objCmd.CommandText = "Exec [sp_UpdateSysproDispatch] '" & TxtOrderNo & "', '" & PurchaseOrderNumber & "', " _
    & "'" & SalesOrderNumber & "', '" & SalesOrderLineNumber & "', '" & SysProDespatchNumber & "', '" & SysProDespatchLineNumber & "', " _
    & "'" & DespatchDate & "', '" & StockItemCode & "', '" & QuantityToRecieve & "', '" & Warehouse & "', '" & SageStatus & "', " _
    & "'" & SysProStage & "', '# & DeliveryDate & #'"
    objCmd.ActiveConnection = adoConn_Sage
    Set objRs = objCmd.Execute

Open in new window


But I get this error: -
SP ErrorCan Anyone help?
0
I am looking forward the position of the cursor with two information: which paragraph, inside which character.
Many thanks.
0
Hello,

I have a form that have multiple listbox with multiple selection and they are filtering subform. Now i want to have filter subform based on date selected in texbox in mainform. VBA code can be use to filter. Also attach is the DB in which frmListBox is the one that have date textbox . When date is selected from that it should filter subform field Timestamp.

Any help would be appreciated.

Thank you.
Wsm93.accdb
0
I had this question after viewing obtain filenames from a folder in ms/access.

Hi
After pasting in the suggested code, I am getting this error that I can't really understand; I have decompiled my code and when trying to compile I am getting the error.
The below screenshos show the source code
can anyone help?

Screen-Shot-2017-08-16-at-20.45.38.png
Screen-Shot-2017-08-16-at-20.57.57.png
Screen-Shot-2017-08-16-at-20.58.14.png
Screen-Shot-2017-08-16-at-20.58.29.png
Screen-Shot-2017-08-16-at-20.58.57.png
0
Columns("B:H").Select
    Selection.Delete Shift:=xlToLeft

I used above code to select columns from B to H and delete it. But its getting select from A to I and deleting all from A-I.

Please advise me code correction.
0
Hi

I need to create an Excel CSV file.  This CSV file has a column of large numbers .....  example 2100000000003   .

I wish for the numbers to be represented in full view.   I need to see the full number so that I know that each number is unique.
 Excel insists on changing the  number to scientific notation.  Of course, they all look the same then.

The application accepting this CSV file will read the numbers as text,

Question... How do I save the CSV file so that I open it again,  I will see the full number instead of the scientific notation representation.

Thank you.
0
Please look at the following VBA code that relates to the attached spreadsheet. The relevant section of this sub is the second paragraph where the first line comment is 'Delete all data in the "TME" tab older than one week. The other paragraphs of this sub work fine as far as I can tell.

This section of the sub refers to the attached sheet, which is the TME tab of a much larger workbook. As you see, the intent of this section of the sub is to delete all records on the TME sheet older than one week from TODAY (the date in column I, Row 1 or relative cell 1,9). For reference, the formula in I1 is =(TODAY()-MOD(TODAY()-1,7))-3. This formula works fine - no need to change it.

The problem is that this second paragraph of the sub below does not actually do any deleting of rows. It also does not stop execution for any errors, but proceeds right on to the next section of the sub without doing anything. I want it to delete all rows from row 2 of the attached sheet and continue deleting until the date in column A is one week older than the date in cell I1.

What needs to be changed to this section of the sub to get it to properly delete the required rows?

Thanks!

Glenn S.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub sub1()
'Import the daily data from the Cognos extract
Dim rng As Range, region As Range, col As Range
Dim rcount As Long, r As …
0
I'm working on a macro that inserts a style separator before the first period in every heading level 2 paragraph. It is also set up to skip any paragraph that is not heading 2. Currently it only works as many times as you specifically trigger it, but I would like it to continue looping until the last style separator is inserted in the last heading 2 paragraph. I can't figure out how to loop this without creating an infinite loop. I'm new to the macro writing game so please excuse my code:

      
 If Selection.Style = "Heading 2" Then
            Selection.MoveUntil Cset:=".", Count:=100
            Application.Run MacroName:="LWmacros.basHNum.InsertStyleSeparator"
            ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

ElseIf Selection.Style <> "Heading 2" Then
             ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

End If 

Open in new window



 I'm not sure what sort of condition I would need to set for the Do Loop for it to end after the last heading level 2 paragraph is found.
0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi!
I need to programmatically populate a Table with the filenames of a specific folder; a sort of a dir command. How do I do it? Any help will be  really appreciated
0
Have this data need query P4
exact macth
with find button  in column S
 then create the P4 output sheet

SHOW Column  d, E, F, G, H ,I, J,K,L,M,N,O,p.q.s.

13 set
show selectED in yellow BACKGROUND
n4mKKK_2A4.xlsm
0
need clean the data in form
29050695_form_A.xlsm
0
after select week number
29050658--1-BB.xlsm
0
need have an week  in c9 form data
Adriana--8-A.xlsm
0
1-when hit button one time need show 1 with red background (Format)
2- when hit button 2 time need show 2 with red background (Format)

here the file
to add the code

Thanks in advanced
buton.xlsx
0
I use VBA to create custom toolbars for MS Office applications.  For Word 2007, 2010, 2013, and 2016, I've always deployed global templates as a .dotm file in the user's AppData\Roaming\Microsoft\Word\STARTUP folder.  For Excel and PowerPoint, either .xlam or .ppam in the AppData\Roaming\Microsoft\AddIns folder.  

Clients are asking me whether these same tools can be made to work in Office 365 where the applications are cloud-based rather than installed on the local machine.

Do global templates and Add-ins work with the cloud-based versions of O365 applications?  Can they be deployed as-is, or do they require modification, or do they need to be recreated in an entirely new language?  Can anybody point me to some documentation on this topic?

Thanks
0
need add an calendar to E9 when i click it
FORM_WORBOOK_N3_1_JJ.xlsm
0
Guys I need help.I need a calendar to my textboxes that in case I will select that textbox it will pull  up the calendar userform.I need it to those date textboxes.I'll be glad if you can help me with this.
hr-form.xlsm
0
Want to be a Web Developer? Get Certified Today!
LVL 9
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Hi Folks,
We have some vba code that opens xls files using an instance of Excel (late binding).
This worked fine with Office 2010 (and prior versions) but with not with Office 2016.
There are no error messages, just a blank workbook.
Navigating to, and opening the file directly from Excel or Explorer works fine.
The code doesn't use any of the optional arguments, and neither 'format' or 'origin' seem applicable.
Any suggestions would be welcome.
thanks
0
Hi ,
I want to select multiple cells and Highlight the column in which the cells are located.

Dim LastColumn As Long
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
Dim LastRow
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 If (Selection.Column >= 1 And Selection.Column <= LastColumn) And (Selection.Row >= 6 And Selection.Row <= LastRow) Then
    Application.ScreenUpdating = False
    With Selection
        If (Selection.Column >= 1 And Selection.Column <= 2) And (Selection.Row >= 6 And Selection.Row <= LastRow) Then
          MsgBox ("Cannot Mark This Area")
        Else
             Dim a
             Set a = Range(Cells(6, Selection.Column), Cells(LastRow, Selection.Column))
             a.Interior.Color = RGB(255, 255, 0)
        End If
    End With
  Application.ScreenUpdating = True
Else
MsgBox ("Cant Mark This Area")
End If

the problem in this code is I can highlight multiple columns
Can you please modify the code that I have.
Thank you
0
Hi Experts!

I want to adjust the width of the all the columns from C2 up to the last column
these are my code

Dim LastColumn As Long
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
Columns("C2", LastColumn).EntireColumn.AutoFit

My problem is
  A.   I have a run -time error on this part
  Columns("C2", LastColumn).EntireColumn.AutoFit
  B.   I have a group cells and I want it to remain it group as I autofit all the cells
0
I have a lot of rows of data and what I'm looking for is on columns G and H I wanted to highlight the entire row red (bad) if the date on Column H is greater than the date on Column G  AND only those red ones imported to a sheet called PASS_DUE_REPORT.

See attachment for what it should look like.  Thank you.....
C--Users-lfreund-Desktop-DATES.xlsx
0
Dear Experts,

Would like to know the best way to code this situation.

So I have six separate combo boxes [cboUnit1Domain1, cboUnit1Domain2, cboUnit1Domain3, cboUnit1Domain4, cboUnit1Domain5, cboUnit1Domain6] on a form. Each combox box populations using this query (SELECT [qryDomains].[StandardLong] FROM qryDomains;) and each combo box is tied to a field in the table.

On the 'onClick' event for one of the comboboxes I have:
 If (([Forms]![frmStudent]![subFrmUnit1].Form![cboUnit1Domain1]) = Me.cboUnit1Domain2) Then
    MsgBox ("This Domain is already being used!")
  End If

Open in new window


The purpose is to ensure that no two combo boxes have the same value. The above code works great if comboBox two value equals combo box one value but coding this for all combinations, e.g. two can't equal one or three or four or five or six and one can't equal two, or three or four or five or six, etc. is a nightmare with "or" statements. I am sure there is a better way so I am reaching out to expert community for answers.

Any suggestions would be most helpful! Thanks!
0
1- what i mean is as i enter the number in column M
need show the numbers in column I J K

2- also i need as i enter data in  C9 need be replicate in C12
29050123aa.xlsm
0

VBA

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.