Go Premium for a chance to win a PS4. Enter to Win

x

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

please see attached file and the screenshot below.

it maybe require a simple modification to the code, but looks very challenging.

EE.pngEE.xlsb
0
Free Tool: SSL Checker
LVL 11
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

I have created a user form called Time Tracker, and been able to link the first combobox (called cboIDList) to the range called ID.

I am having difficulty with the next steps:
  • Autopopulating the combobox called cboPA_Unit, which is to be based on the value that users selects for cboIDList and references the range called PA_Unit.
  • Autopopulating the combobox called cboDesc, which is based on the value that users selects for cboIDList, and references the range called Description
  • Getting data of a completed user form to populate the next available row in a target spreadsheet

For example, if the user opens the user form and selects 2017-0001 in the dropdown for the ID combo box, then the PA Unit and Description combo boxes would both autopopulate with "Unit 1" and "Reduce Publication Files" respectively.

The txtDate field should already be autopopulated with the current date:
  • I would like users to be able to edit the date if necessary, in case they were away and need to back date a record.

Once the user completes the form by entering a value in the 'Minutes' text box, then I would like the data from the form to populate the next available row in the spreadsheet called TrackingSheet as follows:

TrackingSheet!Column A = reflects value entered in cboIDList
TrackingSheet!Column B = reflects value entered in cboPA_List
Tracking Sheet!Column C = reflects value…
0
With excel 2016, I browse for my doc file (eg .docx, pdf, etc) or graphic file (eg .jpg, gif, pttx, etc) via Application.FileDialog(msoFileDialogFilePicker)
and wish to open it with my pc's default application.

This showed me a ShellExcecute API procedure
https://stackoverflow.com/questions/18921168/how-can-excel-vba-open-file-using-default-application

for this is have a reference for a 32-bit machine:
https://support.microsoft.com/en-us/help/170918/how-to-use-shellexecute-to-launch-associated-file-32-bit

but my pc is 64-bit.

Please can you point me to the appropriate solution. (I am not 'agile' with API scripting, more 'cut and paste')
Thanks!
Kelvin
0
Dear Experts:

The following macro (courtesy of the MS Word 'Sage' Graham Skan from EE) applies a blue font color on all table rows where the text entry in the 8th column of the tables is 'blue'

I got a a new requirement:

It is always the last column where this text entry is found. Hence instead of hard coding the 8th column, Line 6 and Line 7 should be tweaked to accomodate the new requirement.

Sub BlueRow()
    Dim tbl As Table
    Dim rw As row
    For Each tbl In ActiveDocument.Tables
        For Each rw In tbl.Rows
            If Len(rw.Cells(8).Range.Text) = 6 Then
                If InStr(1, rw.Cells(8).Range.Text, "blue", vbTextCompare) Then
                    rw.Range.Font.Color = wdColorBlue
                End If
            End If
        Next rw
    Next tbl
End Sub

Open in new window


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

Regards, Andreas
0
Dear Experts:

I would like to apply conditional formatting to selected cells applying the following conditional formatting:

Interior.Color = RGB (243, 17,19)
Font.Color = RGB (255, 255, 255)

The conditional formatting is based on a formula:

=COUNTIF(A$2:A2,A2)=1 'example

The selection is always a vertical selection of cells and in the above example the selected cells start at A2. But of course the selection could also start a B5 and extend to B400 or C2 and extend to C1200

Is there a way to run a macro to apply the above conditional formatting to any vertical selection I wish to make.

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

Regards, Andreas
0
Hey,

I have a form that have two text boxes as Source and Target. The Source take the source path and the destination textbox xopy the file to the destination path. Copying file is working well, i want that when the user copy the file from Source to destination in file dialog picker it also take the file name when the destination is open from source with it's extension. Is there a way to achieve this?

Private Sub txtSource_Click()
 Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    
    Set Dialog = FileDialog(msoFileDialogFilePicker)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtSource.Value)
        .Title = "Select file to copy"
        Selected = .Show
        If Selected <> 0 Then
            Me!txtSource.Value = .SelectedItems.Item(1)
        End If
    End With
    
    Cancel = True
End Sub

Private Sub txtTarget_Click()
Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    Set Dialog = FileDialog(msoFileDialogSaveAs)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtTarget.Value)
        .Title = "Name saved file"
         
        Selected = .Show
        If Selected <> 0 Then
         Me!txtTarget.Value = .SelectedItems.Item(1)
         TargetFile = .SelectedItems.Item(1)
            If Not IsNull(Me!txtSource.Value) Then
                 FileCopy Me!txtSource.Value, Me!txtTarget.Value
                 Me.txtTarget = Split(TargetFile, ":")(1)
      

Open in new window

0
I want to provide the user with the macro in an excel but I do not want them to see my macro codes, is it possible? if yes please suggest.

I want to implement this in Excel and word application both.
0
Hey,

I have a form in which user search for record, this form have a subform which is a datasheet  in which the required search show up. Then there is a button as Edit Record which open new form for editing. I have 9 fields in subform, when user select record and click edit button the new form which open up shows the previous 8 fields except one field "ID" which is always opening as a new record, i want the ID to be  from the previous form not the new record. I have set Data Entry to be Yes which is required in my form, is there a way to get ID from previous form with data entry to yes.

Any help would be appreciated.

Thank you.
0
(1) I'm using Access 2016 , what I want is to use the old menu for 2003 , I can find some some link under the following:

(1) Menu -------------------- Tools  drop down  , here I can see a similar menu like the 2003 Access , but the most wanted one CUSTOMIZE

I do not seam like finding it , I need this one for the purpose of creating my own profession look menu.For sure this will be Achieved by using either VBA or macros to link forms.

(2) I have explored this issue of linking forms & reports using either the macros and VBA through the ON  ACTION  command property , but it looks like the way to link the forms or reports is to create some functions like below:

Public Function OpenForm(strform As string)
On error resume next
DoCmd.OpenForm strform
End function

Now is there a way to amend the function so that I do not just open one form , it should be used to open many forms , otherwise it will mean writing over a hundred functions , I have also explored the macro way , there is also no way out each macro must have its name specifically to accomplish a certain tusk.

Regards

Chris
0
Using a control in MS Access 2016 that is anchored (the properties for Horizontal Anchor and Vertical Anchor are set to "Both").

But when you try to take the .Width property of the control, it only returns the width (in twips) of the control at the time it was drawn. If the size of the control changes due to the anchoring, the .Width property does not change. It's stuck at the original value.

How to obtain the width/height of a control that has been dynamically resized using anchoring?
0
Ask an Anonymous Question!
LVL 11
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Let's say I have multiple modules for my code Module_1, Module_2, and Module_3.

**THIS IS JUST QUICK IN A HURRY SAMPLE BUT GIVES THE IDEA**
If in each Module at the beginning it say If error Goto Errorhandler:
Public Sub Module_1 ()
On error Goto Errorhandler
 Call Module_2
Exit Sub
Errorhandler:
 Yadda Yadda
Exit Sub

Open in new window


Public Sub Module_2 ()
On error Goto Errorhandler
If "" & Value1 = "" Then
 Call Module_3
End If
Exit Sub
Errorhandler:
 Yadda Yadda
Exit Sub

Open in new window


Public Sub Module_3 ()
On error Goto Errorhandler
 'Do Something Here
If Me.Dirty Then
Else
Exit Sub
Errorhandler:
 Yadda Yadda
Exit Sub

Open in new window


Then if there is an error in Module_1 or Module_2....
Which Module will the error handler come from?

This is just a little confusing and I am trouble shooting some modules.
Thanks for the help.
0
I had this question after viewing VBA modification instead of too many OR function, how to use array {2011, 2012, 2013}.

Sub macro3()
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set ShD = Sheets("Data")
For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
    If (ShD.Cells(C.Row, intYearCol).Value >= 2011 Or ShD.Cells(C.Row, intYearCol).Value <= 2013) _
            And ShD.Cells(C.Row, intMonthCol).Value < 111 And _
            ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
        mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
    End If
Next
Sheets("Main").Range("B3") = mySum
End Sub

Open in new window


how can i add a progress bar for this code. i want the progress bar to show the progress when the code is running.

thanks.
0
Hi Experts,

I have 4 number fields as follows

Shift_From_Hours (2 digits)
Shift_From_Minutes
Shift_To_Hours (2 digits)
Shift_To_Minutes

What is the best way to perform calculations like get the qty of minutes between the shift_from and shift_to fields?
0
I had this question after viewing Modification of VBA to use named range instead of offset.

I have this great piece of code from Rgonzo1971.

while the solution, i have is perfect.  for my learning purposes. i wanted to know how can i change this so that instead of many OR and AND functions, i simply use an array for example lets say if i have more years to add then i simply use the  {2011, 2012, 2013}  for year like this ShD.Cells(C.Row, intYearCol).Value = {2011, 2012, 2013}   and for months to exclude ShD.Cells(C.Row, intMonthCol).Value <> {111, 114, 115}

is this something even possible?  because i have seen it in formulas but not in VBA.

Sub macro3()
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set ShD = Sheets("Data")
For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
    If (ShD.Cells(C.Row, intYearCol).Value = 2011 Or ShD.Cells(C.Row, intYearCol).Value = 2012) _
            And ShD.Cells(C.Row, intMonthCol).Value <> 111 And _
            ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
        mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
    End If
Next
Sheets("Main").Range("B3") = mySum
End Sub

Open in new window

0
Hi

I have a string sRange that holds a range. If it holds eg R[-1]C[+2] what VBA would I use
to test if it is an R1C1 reference?
0
Dear Experts:

I got a big word document with hundreds of tables.

Dozens of the rows of these 8 column tables have an entry in the 8th column, saying 'blue'.

I now would like to run a macro that performs the following action.

Whenever a row in any of these tables has the entry 'blue' in the 8th column, the text entries of the whole row have to have a blue font color (RGB 0,0,255).

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

I have attached a sample file for your convenience.

Regards, Andreas

conditionally_color_text_VBA.docx
0
I had this question after viewing Modification of VBA to use named range instead of offset.

Rgonzo1971 was very kind of helping me many times.  the code  in earlier post was great. now i tried to implement this into my worksheet. it seems like for every cell, i have created two procedures for two cell. please see attached file. but i think there have to be a easiler way to combine all of these into one Sub Procesure. i do not know how to do this. any help is appreciated. if it would not be simplified then it seems like i have to have 16 seperate procedures for each of the cells.

EEE.pngEE.xlsb
0
Hello All,

I am using the below Vlookup code with a For loop so that the vlookup runs until it reaches last row in my excel sheet.But it gives error when the for loop reaches a merged cells(Horizontally merged cell). Can someone help me in skipping the merged cells ?

Dim LastRowG As Double, dealer As Variant, DealerNumber As Variant, f As Double
                
        With Balance.Sheets("Líquidas")
               
           LastRowG = .Cells(.Rows.Count, "A").End(xlUp).Row
           
        End With

For f = 7 To LastRowG
           
        dealer = Balance.Sheets("Líquidas").Range("C" & f).Value
        
 'Checking when the column which is being compared is not empty
        If Not IsEmpty(dealer) Then 
'Checking when the column which is beincompared with is not empty
           If Not (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(dealer, RSC.Sheets("OSB").Range("A2:G1000"), 2, False))) Then
             DealerNumber = Application.WorksheetFunction.VLookup(dealer, RSC.Sheets("OSB").Range("A2:G1000"), 2, False)
           Else
             DealerNumber = 0
           End If
        Else
          DealerNumber = 0
        End If
        
        Balance.Sheets("Líquidas").Range("AH" & f).Value = DealerNumber
            
Next f

Open in new window

0
I had this question after viewing Modification of VBA to use scripting dictionary instead of Evaluate(Formula).

Rgonzo1971 was so kind helping me on the code below.  it currently uses offset function. it works if my data columns are static.

sometimes my data columns move around and for that i have dynamic row and dynamic columns named ranges.  is it possibe to modify the code so that instead of using offset. it uses the named ranges which is in the attached workbook.

Many thanks.

Sub macro1()
For Each c In Range(Sheets("Data").Range("a2"), Sheets("Data").Range("a" & Rows.Count).End(xlUp))
    If (c.Value = 2011 Or c.Value = 2012) And c.Offset(, 1).Value <> 111 And _
            (c.Offset(, 2).Value Like "[5-7]*") Then
        mySum = mySum + c.Offset(, 3).Value
    End If
Next
Sheets("Main").Range("B2") = mySum
End Sub

Open in new window

EE.xlsb
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I have created a form menu in my application which has buttons created where people are able to choose where to go , but they are created using the macro wizard not VBA.

(1) Is it possible to hide  these buttons , for example , the objective here is that there some button which are allocated to administrators, accountants , managers  and data entries clerks , I want my form to show only those buttons  relating to user rights. For example the current application does not allow anyone to use all the buttons unless one is not a data entry clerk.

(2) I was also thinking of ways of creating drop downs menus  instead of buttons is it possible in access? I do not to use access built switchboard , it is not okay for huge projects

Regards

Chris
0
Dear Sir,

I am using below code for filtering some data. However, i wish to filter it by multi select from the List (ListPorts).
Kindly help and oblige.

Private Sub cmdSearch_Click()

Dim task As String
Dim varitem As Variant
   

    task = "SELECT ([tblpurchase].[pur_qty]-(select NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Salable,"
    task = task + " Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Sale, "
    task = task + " Val((select NZ(sum(tbllifting.[Lift_qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Lifted, "
    task = task + " Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No])) AS BEFiled, "
    task = task + " ([tblpurchase].[pur_qty]-(select NZ(sum([tblLifting].[Lift_Qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Net_Unlifted, "
    task = task + " Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No]))-Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'Tax' and tblsale.[po_no] = tblpurchase.[PO_No])) AS Tax_Stock, "
    task = task + " Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'BT' and tblsale.[po_no] = tblpurchase.[PO_No])) AS BT_Sale, "
    task = …
0
excel vba
I have data i'm copying to another workbook, from my current workbook.

Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

  
Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Workbooks.Open _
fileName:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx"


If ActiveSheet.Name = "Project Data" Then
Else
Sheets("project Data").Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P


Workbooks("Customer_Template.xlsx").Close SaveChanges:=True
Application.CutCopyMode = False

Open in new window



What I need:
after i copy data i need to insert a column at  Column P in the other workbook.
And give the Column Header a name "Customer  Price"


Thanks
fordraiders
0
I had this question after viewing How to get the same result given by formula vba Scripting Dictionary?.


Rgonzo1971 was so kind helping me, wrapping the formula into Evaluate method to output the result in a cell.

i used debug.print timer in the begging of my code and at the end, for many formulas like this. it takes quite a lot of time.

i was wondering if there is any easy solution with scripting dictionary to extract the same result but not using the formula.

if it is not possible or takes too much time, then i can live with this, otherwise if it is possible for scripting dictionary then it will really make the code faster.   for just one formula, i can replicate for my other formulas. all i need is just for this one formula.

I am not good at coding, but i found use of scripting dictinary very effective and fast.
for example,  VBA to remove duplicate with formula was too slow and with scripting dictionary was many time faster than formula method.

thanks very much for your help.
EE.xlsb
0
Please see attached file.

currently  i get the correct result via formula.  but it is too slow. i need help to be able to generate the same result via VBA scripting dictionary. or any other way that would be via VBA.


=SUMPRODUCT(DataAMOUNT,--ISNUMBER(MATCH(DataYear,{2011,2012},0)),1-ISNUMBER(MATCH(DataMonth,{111},0)),--ISNUMBER(MATCH(LEFT(DataPRODUCT&"#",1),{"5","6","7"},0)))

Open in new window

EE.xlsb
0
I had this question after viewing Vba to search in column B and if unique value is just one then return it to cell A1 of sheet1.


this code

gives error on .  if there one unqiue value in column B starting from B2 then it should return that unique value in Cell A1 of Sheet1

currenty it gives error.  runtime 13 sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)

Sub macro2()
Set objDict = CreateObject("Scripting.Dictionary")
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
Set Rng = Range(sh2.Range("B2"), sh2.Range("B" & Rows.Count).End(xlUp))
For Each c In Rng
    If WorksheetFunction.CountIf(Rng, c.Value) = 1 Then
        objDict.Add c.Value, c.Value
    End If
Next

aDict = objDict.Items
sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)
End Sub

Open in new window

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.