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 Experts,

I have 2 sub forms within a main form and trying to accomplish the following
click on a checkbox in sub form1 and should add a new record in sub form2.
then should call the after update sub of a particular control in Sub form2.

Tried the following and does not work.
Private Sub Check1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.Parent.Patients_Daily_Medications_SubFrm.Form.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me.Parent.Patients_Daily_Medications_SubFrm.Form.Medication = Me.Medication
    'call Me.Parent.Patients_Daily_Medications_SubFrm.Form.medication.Medication_AfterUpdate()
End Sub

Open in new window

0
OWASP Proactive Controls
LVL 12
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

I had this question after viewing Replacing text in PowerPoint 2016 using VBA.

Is it possible that I can insert a custom textbox in slide theme and it gets updated in actual slides ?
Here what I am trying to do in power point VBA which I have done successfully in Visio VBA.

I want to put the presentation file name on all slides of a presentation.  I have 50+ such presentations containing 25+ slides in each presentation.  The above code requires that I insert a text box on each of the slide manually and then it works if I run the macro.  Any suggestion how to automate the process using Powerpoint VBA ?

I do not want to change the slides theme for each presentation by inserting a text box with file name.  That would defeat the purpose of automation. Visio VBA does it very easily by using code in Auotexec module for each slidepack.
0
In my last question I was asking how to import data from an excel file to a table in access 2013.  The solution provided worked great.
So now we have a new challenge.  How do I append the data that was in the table that was created from the excel import to another table using VBA.
I know how to use an append query, but I need this done via VBA.

I am guessing it would look something like this:

CurrentDb.Execute "INSERT INTO  tbl_ParentInventory (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM ImportedFile

Thanks
John
0
All,

I have a test sheet based off another sheet that I have. What I need to do is have the 3 columns display only the data that references what is in the lookup field. For example, if "xyz" is in the error column, I need the data for the 3 columns to display on the lookup sheet, and display all rows.
sampleBook1.xlsx
0
Hi,

I create excel report using ms access. Basically i automated the entire process of transfer queries on access database into excel spreadsheet and format it using vba code. I run these vba code from  access vba editor. So one workbook has got multiple sheets.

Now, I need to do a vlook up on one of the columns in a sheet  and drag it till the bottom cell using a vba script which i can include in my access vba editor.

The lookup table is on the same woorkbook but in a different sheet.

Hope you got the point

Thank you
A
1
In the attached example, I need to copy range B2:E2 and then paste the values to the right of todays date that is located in column G.
Example-File.xlsx
0
I'm trying to convert this 32 bit code into something that can be ran in both 32-bit and 64-bit Microsoft Access.
Based on this website and what I have - I tried to apply those changes but still getting red.  I know if I have the right syntax the red should be gone.

What is the correct syntax I need to incorporate to allow this to compile with no errors please?

Here's the code and provided a screen shot of the highlight:
[CODE]
#If VBA7 Then
  Private Declare PtrSafe Function LoadCursorLong Lib "user32" Alias "LoadCursorA" (ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
#End If
#If Win64 Then
   Private Declare Function LoadCursorLong Lib "user32" Alias "LoadCursorA" (ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
#End If

#If VBA7 Then
  Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
#Else
  Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
#End If
[CODE]
helpwithcode.PNG
0
I know how to use VBA code to import data from an excel spreadsheet to an access table.  All of it work great.
How do I tell the code that the headers are on row 3 and skip rows 4 and 5.  If its possible.
0
In Access VBA function DateDiff- the firstweekofyear parameter can be set to vbUseSystem - but where can I check what the NLS API setting is set to ?
0
Hi

I know we have a good number of experts here and very experienced, now I need guidance on the VBA function that can retrieve the data from Json format like below:
{
"code": "500",
"Naration": "OK",
"declared-info": {
"Receipt-code": "3333333",
"Receipt-number": "111111",
"Customer-ID": "1025360",
"Customer-vat ": "78963102",
{

I hope you can see the parameters above, the VBA function when run need to grab the data from those parameters from the point of sales machine (POS). The accomplishment here is to ensure that the receipts that are created from the POS machines are not recaptured into Ms Access Application , instead they must be automated to avoid errors, this is the requirement by our client of which we do agree the concept.

Regards

Chris
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Attached is an Excel document, Excel_EqualWidthCells.Xlsx that has two sheets, one labeled "Structure".
The other is labeled, "Header-Footers", and looks like this:
Headers	Footers	 -- There is inconsistent use of "…", so assume that "…Begin" and "Begin" are keywords
…Begin	…End	 -- Notice the "…" acts like it is a singe char
…If		         -- Assume case insensitivity, so "BEGIN" and "begin" are also matches
…For		

Open in new window

For simplicity, I can remove the "…" from the document. Other tables have different keywords for their headers/footers, so I'll just change the corresponding headers and footer keywords in the VBA code.

The attached Excel_EqualWidthCells---ManualInden.xlsx document shows the desired result after the indentation macro is run.

The Excel_EqualWidthCells.xlsx file is very flat looking, yet describes a complex structure using headers and footers. I manually indented sections of rows so that I could see the structure. This is tedious, but doable.

One suggested approach in a different question is found here.
https://www.experts-exchange.com/questions/29131489/Use-keywords-and-expressions-to-indent-nested-structures-in-Excel.html#a42769597
"This can be done with a stack structure, where you push a set of items on the stack when some condition is detected and remove them when another condition is detected.  In your case the first set of conditions is a cell that begins with ("loop", "if") and the second set of conditions is a cell that begins with ("end").  If you're working in Excel, you might use the stack 'level' (depth) as a parameter in an .Offset() method.

"With as simple a problem as this one, your stack structure can be an integer variable.  You might indent as you iterate the rows."
0
Attached below is a Word doc with a sample table that I try to copy to Excel. One row in the Word table may become multiple rows in Excel. That causes other problems when processing the Excel table. Ideally, the copy to Excel gives one row for each ACTION FIELD entry as it does in this Word table.

Word Table showing first 8 rows:
Shows first few lines of Word Table
Excel Table after copying first 8 rows has 22 rows. I would like Excel to have only 8 rows, since that is what Word had.
Shows first few lines of Excel Table - too many rows
Both the full sample Word table, and the attempted copy to Excel (with 22 rows) is attached.
Word_to_Excel_Issue.docx
ExcelHasTooManyRows.xlsx
0
I would like to add the following into the VBA code below.  If B6=Yes, then J32;J33=NA.  The below code works but I don't want to screw it up.  Any help is appreciated!



If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J19"
            If Target = "Select" Then
                Range("J20:J21").Value = "Select"
            ElseIf Target = "No" Then
                Range("J20:J21").Value = "NA"
                Range("J37:J41").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J20:J21").Value = "Select"
                Range("J37:J41").Value = "Select"
            End If
        Case "J20", "J21"
            If Target = "" Or Range("J19") = "No" Then
                Range("J19:J21").Value = "Select"
            End If
    End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J22"
            If Target = "Select" Then
                Range("J23:J24").Value = "Select"
            ElseIf Target = "No" Then
                Range("J23:J24").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J23:J24").Value = "Select"
                Range("J37:J41").Value = "Select"
            End If
        Case "J23", "J24"
            If Target = "" Or Range("J22") = "No" Then
              …
0
I would like to add the following to the below VBA code: if J19 or J22 is "Yes", then change J41:J45 to "NA".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J19"
            If Target = "<Select>" Then
                Range("J20:J21").Value = "<Select>"
            ElseIf Target = "No" Then
                Range("J20:J21").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J20:J21").Value = "<Select>"
            End If
        Case "J20", "J21"
            If Target = "" Or Range("J19") = "No" Then
                Range("J19:J21").Value = "<Select>"
            End If
    End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
    Select Case Target.Address(0, 0)
        Case "J22"
            If Target = "<Select>" Then
                Range("J23:J24").Value = "<Select>"
            ElseIf Target = "No" Then
                Range("J23:J24").Value = "NA"
            ElseIf Target = "Yes" Then
                Range("J23:J24").Value = "<Select>"
            End If
        Case "J23", "J24"
            If Target = "" Or Range("J22") = "No" Then
                Range("J22:J24").Value = "<Select>"
            End If
    End Select
End If
Skip:
Application.EnableEvents = True
End Sub
0
Wasn't there a setting in Microsoft Access whereby you change a field name it would go and update any query or form that had that field - updating to the new name?  

And if you turned that setting off thereby it would make Microsoft Access run faster?

Do any of you recall what that setting's name was or what I am talking about?  And if I get the technical name of it - then I do a google search on some VBA code to turn off that feature... or if you know it already please paste here.

Btw is there some VBA scripting out there that does the above automatically (without paying for a license)?
I've already did the scripting to change table and fieldnames automatically.  But then I have to go in and change all the queries and forms where the previous names were.

Thank you in advance...
0
Hello,

I have a Word form that has a list of options that I want to put as three content controls drop-down lists. The second depends on the first and the third depends on the second. Is it possible to do this through a VBA code? I managed to create code but I used the legacy Drop-down form field and the end form doesn't look as good, the reason why I need to do it through content controls. I also have an excel file with all the possibilities to be chosen. I'm sorry if I'm not super technical, I'm new to all this. Can someone, please, help?
Career-path-EN-FR-13-11-2018.xls
0
I want to make it so people can’t copy/paste from cell to cell?  I have all the cells locked accept for the cells that will have drop down menus and the sheet protected.  I want to prevent people from copying from one cell to another because each cell has a slightly different answer choices in the drop down menu.  

Thanks!
0
Access VBA - looking for a way to apply a filter to a subreport in a report with vba or alternately a way to change the record  source for a subreport with vba
0
I'm sure there is an easier way to do what I'm attempting....  I want J19:J21 to default to "Select".  If J19=No, then NA for J20 and J21.  If J20 or J21 change from NA while J19=No, then change J19:j21 back to Select.  If J19:J21 are changed to blank, then default back to "Select".  If J19=Yes, then J20:J21 default to Select.
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Hi Experts,
In Access 2010, I am trying to write additional VBA code within a command button click event that opens another form, that will change the control source of a text box in the new form.   And I'm not sure if the code should go in the click event or on the load or open events of the new form.  Any help is greatly appreciated!  Sample of problem in attached database.  Thank you!Database_e.g.ChangeTextBoxControlS.accdb
0
Hello experts,

I have the following procedure in order to unify chart format.


Sub CopyChartFormats()
'Updateby20140219
Dim Ws As Worksheet
Dim Cht As ChartObject
Dim xChart As Chart
Dim bTitle As Boolean
Dim bXTitle As Boolean
Dim bYTitle As Boolean
Dim sTitle As String
Dim sXTitle As String
Dim sYTitle As String
Dim iSource As Long
Dim iTarget As Long
Dim iTotal As Long
Dim iSeries As Long
Dim vSource As Variant
Dim vTarget As Variant
Application.ScreenUpdating = False
Set xChart = Application.ActiveChart
iSource = xChart.SeriesCollection.Count
Set Ws = Application.ActiveSheet
  For Each Cht In Ws.ChartObjects
    If Ws.Name = xChart.Parent.Parent.Name And _
        Cht.Name = xChart.Parent.Name Then
    Else
      With Cht.Chart
       iTarget = .SeriesCollection.Count
        bTitle = .HasTitle
        If bTitle Then
          sTitle = .ChartTitle.Characters.Text
        End If
        If .HasAxis(xlCategory) Then
          bXTitle = .Axes(xlCategory).HasTitle
          If bXTitle Then
            sXTitle = .Axes(xlCategory).AxisTitle.Characters.Text
          End If
        End If
        If .HasAxis(xlValue) Then
          bYTitle = .Axes(xlValue).HasTitle
          If bYTitle Then
            sYTitle = .Axes(xlValue).AxisTitle.Characters.Text
          End If
        End If
        xChart.ChartArea.Copy
        .Paste Type:=xlFormats
        iTotal = .SeriesCollection.Count
        If iTotal = iSource + iTarget Then
          For iSeries = 1 To iTarget
   

Open in new window

0
Dear Expert:

I got a smart table (named tbl_overview) on a worksheet called 'overview'

One of the column headers of this table has the caption 'Check_Column'. This column is hidden. The values in this column are either 'True' or 'False'

I also got a regular checkbox (not an active x- element) on that worksheet 'overview'). It is named 'chk_box_hide_unhide'

I would like this checkbox 'chk_box_hide_unhide' to have the following functionality:

If checked:  all the rows of the named smart table 'tbl_overview' that have the value 'False' in the column (Check_Column) are to be hidden
If unchecked: all rows have to be shown

I guess a macro code has to be assigned to the checkbox, am I right?

Help is much appreciated.

Thank your very much in advance.

Regards, Andreas
0
excel 2010 vba

The data is being grabbed off a worksheet

Trying to catch a negative number and do stuff with it..

Dim CellDataEvaluate As String
' for example
CellDataEvaluate =  -0.82
 


' catch a negative number
 If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") And InStr(1, CellDataEvaluate, "-") < 0 Then   ' this should catch a negative string
                             CellDataEvaluate = CDec(CellDataEvaluate)

The line above is not catching a negative number ?


Thanks
fordraiders
0
I have the below/attached employee table in an access database.  I need a query to find all the direct reports and indirect reports of a particular employee

EmpID      Fname      Lname      MngID
1      Fred      Flinstone      
2      Linda      Thompson      1
3      Sam      Adams      1
4      Jason      Williams      1
5      Michael       Jordan      2
6      Katey      Perry      5


If I wanted to find all the direct and indirect reports of EmpID 2,  it should return michael and Katey,   Excel or access should be ok


This is what I want to do but in access SQL Query to find all the subordinates of a superior
0
Dear Experts:

My current workbook is a multi-worksheet workbook.

All of these worksheets (with the exception of a worksheet named 'Overview) contain smart tables which I have named such aus 'MT_Surgery' or ' CT_Scissors' etc.

I now would like to retrieve all the names of these smart tables along with their location, i.e. which worksheet these named smart tables reside in.

This data is to be compiled in the worksheet named Overview.

So the result should look like this in the worksheet called 'Overview'

Example


Retrieve_Names_Smart_Tables_And_Corresponding_Worksheets
I guess this can only be achieved using a macro.

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

Regards, Andreas
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.