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

Hello, I am attempting to add vba to a command button to look at a text box and if not null, then look at a combo box. If that combo box is null, have a pop up message box and not allow the user to save the record. Since it is a continuous form and there are several records showing, it works fine on the first record, however it does not for the following records. I believe this is because I need to loop through each record. Below is the VBA I have so far. I would greatly appreciate any help regarding the loop, as I am new to VBA.

If Not IsNull(Me.Text33.Value) Then
   If IsNull(Me.Combo24.Value) Then
MsgBox "You must state if the rationale is aligned with procedures for each disputed subcategory."

Else
'do nothing
End If
End If

DoCmd.Save
DoCmd.Close
Thank  you in advance,

Susan
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[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 had this question after viewing Automate web from excel.

Below code works perfectly fine; however what I am looking for is click on the print option and save as pdf (name of pdf needs to be picked from Column C

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Const READYSTATE_COMPLETE  As Long = 4

Private m_WebBrowser As SHDocVw.WebBrowser

Public Sub CheckVats()

  Dim ws As Excel.Worksheet
  Dim RowCount As Long
  
  Set ws = ActiveWorkbook.Sheets("Sheet1")

  For RowCount = 1 To ws.UsedRange.Rows.Count - 1
    InitializeWebBrowser
    SubmitVat ws.Range("A1").Offset(RowCount, 1).Value, ws.Range("A1").Offset(RowCount, 3).Value
    ws.Range("A1").Offset(RowCount, 0).Value = ValidationResult()
  Next RowCount
  
  Set ws = Nothing
  FinalizeWebBrowser
  
End Sub


Private Sub FinalizeWebBrowser()
  
  'ToDo: Close
  Set m_WebBrowser = Nothing

End Sub

Private Sub InitializeWebBrowser()

  If m_WebBrowser Is Nothing Then
    Set m_WebBrowser = CreateObject("internetexplorer.application")
    m_WebBrowser.Visible = True
  End If
  
  m_WebBrowser.navigate "http://ec.europa.eu/taxation_customs/vies/vatRequest.html"
  Do While m_WebBrowser.busy
    DoEvents
  Loop
  
  Do While m_WebBrowser.readyState <> READYSTATE_COMPLETE
    DoEvents
  Loop
  
End Sub

Private Sub SubmitVat(ACountryCode As String, AVatID As String)

Open in new window

0
Hi
   How to create a macro for exporting the excel data into access database. Can anyone please help me.
0
I have two questions regarding hyperlink vba coding.

Question 1:

I would like to display the text "Click here" instead of the actual URL in each row. What do i need to do to achieve this?

Currently i am using the following code to generate the hyperlink

Dim i3 As Integer
Dim s3 As String

For i3 = 4 To Range("H:H").Cells.SpecialCells(xlCellTypeLastCell).Row
    s3 = "https://maps.google.com/maps?q=" & Trim(Range("H" & CStr(i3)).Text)
    ActiveCell.Hyperlinks.Add Range("Q" & CStr(i3)), s3
    Range("Q4").Font.Color = RGB(248, 248, 248)
    Range("Q4").Font.Underline = False
    Range("Q4").Value = "GoogleMap"
Next i3

Open in new window


Question 2:

What do i need to add in the code to display the text "BLANK" if the hyperlink only contains this

"https://maps.google.com/maps?q="
0
i have an sheets some data is mark with the color can i  copy just by the color ??
0
If I have a spreadsheet of data whereby column A gives me the name of someone, and column E gives me the answer 'yes' to liking ice cream; can I populate a new worksheet with the name provided in column A, provided the condition is met that Column E is 'yes'?
0
I cannot close each pdf file separately
OpenClosePdf.accdb
0
Hi all, could me please tell me how to create dll file using vba.net. I need to create dll file and use in word vba. I have create dll file and gave reference via word vba but Activex can't create objects error coming..

ex:
Dim tem as testing.test
Set tem = New testing.test

error coming in second line
0
I am automating the printing of 50+ reports to specific printers based on the report that I will be printing. The specified printer is not currently listed in my printer selection list. This is due to a thin client that upon logging in resets the list to default printers in the area. I want to automate the setup of the network printers I currently have to manually setup every day.
0
I have an app that writes an XML file, then FTP's it. It normally works OK but running on a different PC I get an error: -Access-Error.png
This crash seems to happen just as it starts writing the text file. Can anyone help?
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Hello Experts -

     For some reason the code that I have is not fully minimizing outlook when the macro is engaged.  When I click the button, I'd like just the macro its self to remain and to minimize Excel completely in the background.  Where am I going wrong with my code?

Sheet 1:
Private Sub btnFrmopen_Click()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show vbModeless

End Sub

Open in new window


Module 1:
Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long

Private Declare Function GetWindowLong _
  Lib "User32.dll" Alias "GetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long) _
  As Long
               
Private Declare Function SetWindowLong _
  Lib "User32.dll" Alias "SetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) _
  As Long

Private Const WS_THICKFRAME As Long = &H40000
Private Const GWL_STYLE As Long = -16

Public Sub MakeFormResizable()

  Dim lStyle As Long
  Dim hWnd As Long
  Dim RetVal
  
    hWnd = GetForegroundWindow
  
    'Get the basic window style
     lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME

    'Set the basic window styles
     RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)

End Sub

Open in new window


Your suggestions are greatly welcomed! Thank you.
0
Hi,
i have an one .exe tool. it has Name,id,Sex etc. whenever i fill the data and click the save button the data will save my DB. The Question is I have a dump excel file. in that excel file have Name, Id, Sex columns, i want to create the macro. once i start the macro the tool will automatically open and fill the data and save it. Please help me on that. Am the beginner for the VBA.
0
I am making slow progress with my treeview. The latest problem is the first child node repeating until Access give an error 3014 'Can't Open Any More Tables'


Capture-6.PNG
What am I doing wrong?

Private Sub addChildren(tv As TreeView, nodParent As node, rsReqs As DAO.Recordset, lngParentID As Long)
   Dim strFind As String
   
   Dim strSQL2 As String

  Set rsReqs = CurrentDb.OpenRecordset("Query2")
   
   
   strFind = "HeaderID=" & lngParentID
   rsReqs.FindFirst strFind
   Dim nodX As node
   Dim strBook As String
   rsReqs.MoveFirst
    Do While Not rsReqs.NoMatch
      Set nodX = tv.Nodes.Add(nodParent, tvwChild, , Left(rsReqs!StockCode, 50))
      strBook = rsReqs.Bookmark
      addChildren tv, nodX, rsReqs, rsReqs!HeaderID
      rsReqs.Bookmark = strBook
      rsReqs.FindNext strFind
   Loop
   
   
   
   
End Sub

Open in new window

0
Hi

I'm trying to find a way to create command buttons via VBA. I would also like to rename them and attach a code to them too.

I would like the code to sit within the worksheet.
0
Hello together,

i have a Excel list with a Hyperlink in column B. and a Name in column A

i would like to have a Code witch should do the following:


1. check a defined "Masterfolder", if there is a file witch named like the Name in column A (.pdf, .zip, ,xls,...)
2. if yes than store the file according to the Hyperlink in column B, open a msgbox and tell me how many files are copied
3. if no open a msgbox and tell me that you didn´t found any files witch matches.
4. if the targetfolder already have files with the same Name, open msgbox and ask if the files should stay or if you would like to override the existing files.
5. if you would like to leave the existing files, ask to delete the files in the "Masterfolder"
6. delete the copied files from Point 3 in the "Masterfolder"
7. if the Code couldn´t delete a file, becauce it´s in use, open a msgbox

The Code should be started by a button and should check all entries witch are visible in the actual autofilter


hopefully this is more or less comprehensible for you ;-)
otherwise i will try to explain it more detailed.

thank you in advance for your Support

regards
Tobias
0
Is there a way using vba to copy a sheet tab from a closed workbook into a currently open workbook?  Is this even possible?
0
Outlook 2016 apparently has removed the ability to create an icon with a hyperlink to a network folder. We have a folder on our network that has commonly used Outlook templates. I would like to use VBA to create a link to that folder in Outlook as a quick access macro. I am not a VBA coder, but I hope somebody could point me in the right direction.

Thanks,
0
Hi experts,
Pls check the attached sample excel sheet. would like to get ur help.

Sample sheet.
1. I have 3 rows of data in several columns
2. I also populated Column O "Expected Final Result" on how I would like to get the values aggregated
3. Basically scan each row by every column and summaries on the final "Expected Final Result" column
4. "Expected Final Result" column position might change, so better to have relative cell
5. Since trying to automate this. Is it possible via excel formula? or vba?

Thanks in advance
sample.xlsx
0
Hi

I have Excel VBA code that populates several page lengths of data on a single spreadsheet.
Is there a way to detect which lines page breaks are automatically inserted at?

Thanks
0
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Experts,

I have an Access split database with a form that has a record source that is a crosstab query. When I open the form and do nothing else, the query runs and populates the form, and also locks the back end. I want to leave the back end unlocked so I don't have to call everyone who may have the form open in the database and ask them to close their client before I can make changes the the back end.

How do I open the form without locking the database?

Thanks
0
Hello,

I am facing issue bellow issue in different PCs. only one pc it is working and that is having Excel 2010

could not load object because it is not available on this machine in excel 2016&2013

Please find attached the images for reference.
1.JPG
2.JPG
0
Hello-
  I have a form.   I need the user to pick a SITE,  then have the USER field on the form update, so User can be picked.

For example, if  the NORTH LOCATION is picked, I need all the users assigned to that location to show up.


Will be using this for a report, I have been playing with the ACCESS UPDATE function in the code, I am just not doing it right.

I have a sample database attached so you can kind of see what I need.


My SECOND question is:  one this form is done, how do I connect it to a report?
FORM.docx
FORM_SAMPLE.mdb
0
compare two numerical cells   a & b  if the value in a is less thank the value of cell b then change cell a to the same value as cell b
 ie  a= 4  b=10  change a to =b
0
I want to use Task Scheduler to open a CSV file from a website and save it to a local folder as a different file name.
0
for years i used

    Application.DisplayFullScreen = True

in order to get my display in full screen modus

Recently, something has changed, and now the top row of the menu mentionning the file-name and the formula bar is still visible.
How can I hide these two lines ?
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.