Visual Basic Classic

163K

Solutions

58K

Contributors

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Share tech news, updates, or what's on your mind.

Sign up to Post

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Hi,  When I enter something new or change something in Cell B1, I would like Cell C1 to be shaded yellow.   I tried this with Conditional Formatting, but I believe that you need to have a specific value in order for conditional formatting to trigger the change.  I want this shading in C1 to be triggered if anything is added or changed in B1.   In other words, B1 could be blank or already populated, so if there is any change entered to that cell, I need C1 to alert me by shading it yellow.  I then need to copy it to subsequent rows.  

I hope my request makes sense and is clear.

Thank you!
0
MS-Access 2016 automatically colors rows in their reports.  These colors alternate by ROW.  I want to alternate the colors by GROUP!  I grouped my report by Invoice (Invoice1, Invoice2,....Invoice100) and want to alternate the colors by Invoice , to include their "Subs"

Example:
Invoice1
    Sub1
    Sub2
Invoice2
    Sub1
Invoice3
    Sub1
    Sub2
    Sub3
Invoice4
    Sub1
    Sub2

Invoice1 AND Sub1, Sub2 would be one color (lets say grey)
Invoice2 AND Sub1, would be alternate color (lets say white)
Invoice3 AND Sub1, Sub2, Sub3 would be Same color as Invoice1 (grey)
Invoice4 AND Sub1, Sub2, would be Same color as Invoice2 (white)

Can any one help?
0
I want to be able to create a macro which copy's data from select cells and pastes then into another sheet. I want to be able to keep the existing data from the previous day.  By the end of the week i will have 5 days all in the same sheet.

current macro:

Sub Test()
'
' Macro2 Macro
'

'
    Range("B3:F15").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("H6:I6").Select
End Sub
0
Good Morning,

Yesterday I posted a question about saving file locations within Word VBA, I was very kindly pointed in the right direction SaveSetting \ GetSetting however when I tried the suggested code it didn't work and my knowledge is limited - so I am not even sure if it possible hence posting again

I want to use GetSetting to capture the ActiveDocument.name, ActiveDocument.Path and then pull that info back into a variable so I can use it at a later date but I get "Run Time Error 5 - Invalid Procedure Call or Argument" at the line that read StrPath = GetSetting("CMS", "File Locations", strFileName, "Not found")

The code with Comments at bottom of post is what I am trying to achieve, the code is totally wrong but might help

Sub TestSave2()
'
' TestSave2 Macro
'
'
Dim StrPath As Variant
SaveSetting "CMS", "File Locations", ActiveDocument.name, ActiveDocument.Path
StrPath = GetSetting("CMS", "File Locations", strFileName, "Not found")
ChangeFileOpenDirectory _
"File Locations"
            ActiveDocument.SaveAs2 FileName:= _
        "CMS", FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
End Sub

This code with Comments is what I am trying to achieve, the code is totally wrong but might help

Dim DocName As Variant
Dim File_Locations As Variant
SaveSetting "CMS", ActiveDocument.name ' I want to Save the Document Name
SaveSetting "File_Locations", ActiveDocument.Path ' I want to Save the FileLocation
0
I have two access database with same table and same columns. I wanted to copy records from one database to another one. I was trying the below code in VB6 but it provides "Syntax error in INSERT INTO statement"

I was using ADODB

On Error GoTo er1
Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection
dbs.Open "Provider=microsoft.jet.oledb.4.0;data source=" & dbServer

dbs.Execute My_query


My_query is  
INSERT INTO empTable(JobID, Date, EmpOut, EmpIn)  SELECT 515 AS JobID, Date,EmpOut, EmpIn FROM empTable IN ('D:\Application\data\Employees2017.mdb')

Could anyone suggest me a solution!
0
I am using WinHttpRequest in VB6 the server returns me a Jpg file. I can not load the file into a Picture Box. Can you help me please?
0
Dear All,

I have a problem in calculating scrollbar position.

Issue: I have a Panel control (AutoScroll = True) inside which I have a control which change its size based on scrolwheel movement. (lets assume 1 tick at scroll - square control gets as much as doubled in size). What I want to achieve is to automatically scroll to the same position that mouse was pointing at before zoom. It's not as simple as using zoom factor, as the control inside the panel is bigger than panel.

I'd appreciate any advice.

Steps to reproduce:

- place panel and set autoscroll to true.
- place (lets say picturebox) inside a panel.
- implement any simple formula of changing size based on scrollwheel (ie. double size each step and make ~4 steps)

I want the scrollbars to update themselves after zooming (so the pointer is pointing at the same pixel on above's said picturebox) - I do not want to change pictures's box location - i want to achieve this by scrolls only.

I hope its clear. Thanks for any help given.
0
Hi experts, I'm trying to get the sum of the field but the code below is not working. Is there anyone can help me fix the problem? The code below will always responded yes even have no record. Thanks!

Public Function ObtainTotCreBal(code As String)
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = cn.Execute("Select Sum(Credit) as TotCredit, Sum(Debit) as TotDebit from SuppliersLedger where Status='" & "Unpaid" & "'")
    If rs.EOF Then
        MsgBox "No"
    Else
        MsgBox "Yes"
    End If

Set ObtainTotCreBal = rs
Set rs = Nothing
End Function

Open in new window

0
Hi experts, I wish to know why still got a message of "Yes" base on the codes below even the row is not exist?  I erased all unpaid from the table, SuppliersLedger  but still got a message of "yes" after removing it. Is there anyone who can give idea what's wrong on the codes? Thank you!

Public Function ObtainTotCreBal(code As String) 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = cn.Execute("Select Credit as TotCredit,Debit as TotDebit from SuppliersLedger where Status='" & "Unpaid" & "'")
    With rs
        If rs.BOF = True And _
            rs.EOF = True Then
            MsgBox "No"
        Else
            MsgBox "Yes"
        End If
    End With
Set ObtainTotCreBal = rs
Set rs = Nothing
End Function

Open in new window

0
Free Tool: Site Down Detector
LVL 9
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

According to my work,I have a  folder which contains some pdf files.

1.I need to merge files into 2 separate files.(File names ends with ABCRDF & ABCOSD into single merged file and all other pdf files into another merged file.
2.So after merging we will have only 2 pdf files.One which contains file names ends with ABCRDF & ABCOSD.And other merged file with all  files except above 2 files.
3.Merging should be done by last modified time stamp.

I have code which merge all the files present in a folder into a single pdf based last modified time stamp.It wont differentiate files based on file names.It will take all the files present and merge to a single pdf file.I will attach the same here.


Any help regarding this would be greatly appreciated..!!

Sub Main()

    Const DestFile As String = "MergedFile.pdf"
    
    Dim MyPath As String, MyFiles As String
    Dim a As Variant, i As Long, f As String
     
     ' Choose the folder or just replace that part by: MyPath = Range("E3")
    With Application.FileDialog(msoFileDialogFolderPicker)
         '.InitialFileName = "C:\Temp\"
        .AllowMultiSelect = False
        If .Show = False Then Exit Sub
        MyPath = .SelectedItems(1)
        DoEvents
    End With
     
     ' Populate the array a() by PDF file names
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    Set sh = Workbooks.Add.Sheets(1)
    Set Rng = sh.Range("A1") ' or wherever; doesn't matter

    Set fso = 

Open in new window

0
in an old vb6 application,

When you set a breakpoint in the code it looks like this with a maroon colored marker.

q1.PNG
What does the sky blue marker mean? how do you remove it?

q2.PNG
0
Hello,

This is a follow-up to an earlier question posted here:

Combine multiple sorted Excel worksheets to a single summary worksheet which is also sorted

The main question in that thread was:
Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

Please refer to the earlier thread for detailed examples and screenshots.

The questions below refer to the solution in the previous thread which is also included here for convenience:

Sub CombineSheets()
    ' loop all sheets and combine
    For Each ws In Sheets
        ws.Select
        ' if 1st sheet, clear first
        If (ws.Name = "Combined") Then
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Else
            ' for other sheets, copy and paste into first one
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets("Combined").Select
            
            Range("B4").Select

Open in new window

0
Hi experts, I'm trying to write on the registry editor using vb6 as the code shown below. The code works on Windows Xp but not in Windows 10. Is there other way to achieve my goal?

Option Explicit
Const HKEY_LOCAL_MACHINE = &H80000002
Const REG_SZ = 1

Private Declare Function RegCreateKey Lib "advapi32.dll" Alias "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long


Private Sub Command1_Click()
Call savestring(HKEY_LOCAL_MACHINE, "SOFTWARE\Microsoft\Windows\CurrentVersion\Run", Trim("MYSERVER"), App.Path & "\" & "Me.exe")
End Sub

Public Sub savestring(hKey As Long, strPath As String, strValue As String, strdata As String)
    Dim keyhand As Long
    Dim r As Long
    r = RegCreateKey(hKey, strPath, keyhand)
    r = RegSetValueEx(keyhand, strValue, 0, REG_SZ, ByVal strdata, Len(strdata))
    r = RegCloseKey(keyhand)
End Sub

Open in new window

0
HiMartin,
I had this question after viewing How to create dropdown with autofill/autocomplete in Excel.

Can i use the code you provided  in the the "this workbook" or module in Vb? I have so many sheets and i though its better to do it one time  rather that pasting this to all sheets in VBE.

If it also not too much to ask, may I add in my question; if this code can be done that any string to be inputted in the combo box will provide suggestion options from the data validation drop down  list (really like a google type behavior)?

Thanks,
Reggieneo
0
how to browse in Dos environment
0
Hello,

Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

For example, suppose you have three checking accounts, each with identical column headings and each occupying a separate worksheet  as shown here:

2017-08-03a_EE.png
2017-08-03b_EE.png
2017-08-03c_EE.png
Note that in this screenshot:

• each colored worksheet tab is named named by the last 4 digits of its account number
• the account for each transaction is identified by the same 4 digit code in column B
• the transactions in each account are sorted by date

I'm looking for an Excel formula that will capture the transactions from all three accounts and combine them into a single summary worksheet as follows:

2017-08-03d_EE.png
In this screenshot, note that:

• each transaction is still labeled by its respective account number in column B
• the transactions are in chronological order
• the balance shown in column G is a combination of all three accounts

I'm hoping there is a solution which will populate the Combined worksheet automatically and which involves only Excel formulas since that will make them tweak-able for me. The 2nd option is to use VBA but then I will be dependent on EE for changes. I prefer not to use an Excel add-on since I've found them to usually be un-tweak-able.

Thanks a bunch

File attached:

Combined-Accts_EE.xlsm
1
i'm working on an old vb6 application.

I'm using the vaSpread grid control.

So if i have a vaSpread grid with ID of MyGrid1

how do I setup a button click event that checks to see if the grid contains any data.

So after I press the button,

if the grid has data display this message in a message window:

"The grid has data."

if the grid has no data display this message in a message window:

"Sorry, the grid has no data."
0
I have an old VB6 application that I need to support and enhance slightly. One of the enhancements is error handling. I'd like to pass the entire Err object from VB6 to C#. Passing it as a type "object" does give me the item and you can see in the quickwatch screenshot below that the dynamic view does show all the properties I care about. My question is, how do I translate this into a usable C# class object where I can actually access the properties and their values?QuickWatch
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi Friends,

according to my work,I have number of pdf files present in one folder .I need to merge all the files into a single pdf  file  based on last modified time stamp. Means first modified pdf file as first and then the rest.
I tried with the below code.

Sub Main() 
     
    Const DestFile As String = "MergedFile.pdf" ' <-- change to suit
     
    Dim MyPath As String, MyFiles As String 
    Dim a() As String, i As Long, f As String 
     
     ' Choose the folder or just replace that part by: MyPath = Range("E3")
    With Application.FileDialog(msoFileDialogFolderPicker) 
         '.InitialFileName = "C:\Temp\"
        .AllowMultiSelect = False 
        If .Show = False Then Exit Sub 
        MyPath = .SelectedItems(1) 
        DoEvents 
    End With 
     
     ' Populate the array a() by PDF file names
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" 
    ReDim a(1 To 2 ^ 14) 
    f = Dir(MyPath & "*.pdf") 
    While Len(f) 
        If StrComp(f, DestFile, vbTextCompare) Then 
            i = i + 1 
            a(i) = f 
        End If 
        f = Dir() 
    Wend 
     
     ' Merge PDFs
    If i Then 
        ReDim Preserve a(1 To i) 
        MyFiles = Join(a, ",") 
        Application.StatusBar = "Merging, please wait ..." 
        Call MergePDFs(MyPath, MyFiles, DestFile) 
        Application.StatusBar = False 
    Else 
        MsgBox "No PDF files found in" & vbLf & MyPath, vbExclamation, "Canceled" 
    End If 
     
End Sub 
 
Sub 

Open in new window

0
Hi,
I Have 2 MSHFlexgrids. One is a list without Rank's in column 4 and the other grid is a Database with Ranks.

The objective of the macro would be to take each Name and color from MSHFlexgrid1 and see if we have a match in MSHFlexgrid2. If we have a match, we need to look at the Rank assigned on MSHFlexgrid2 and provide new Rank to new records in MSHFlexgrid1.

The big challenge is to start counting from 1 going up.

Ex: If in MSHFlexgrid2, I have:
Name: Bruno
Color: White
Rank:2

and

Name: Bruno
Color: White
Rank:3

and in MSHFlexgrid1, i have
Name: Bruno
Color: White
Rank:

The new Rank for Bruno, White in MSHFlexgrid1 would be "1".
If I would have a second Bruno, White in MSFlexgrid1, the rank would be 4 because we already have a Bruno,White 2 and 3 in MSHFlexgrid2.

If in MSHFlexgrid 1 dont have any match in MSHFlexgrid2, then it would assign a rank starting at 1 going up.

Here is an example that you will found in sample project in attachment:
Before:
Find Rank Before
After macro:
Find_Rank_After  

MSHFlexgrid2 will always be sorted by Name, Color, Rank, and MSHFlexgrid1 not always sorted by Name and Color and the Rank is missing.

How can i do that?

Thanks for your help
Find-Value-in-cell.zip
0
Hello...

I am running MS Access 2000 and Outlook 2000 on an XP machine.

I made a change to the reference for the Outlook Object Module which was to select the MSOUT.OLB file instead of MSOUTL9.OLB.

MSOUTL.OLB is the Outlook Object Library that comes with Win10... MSOUTL9.OLB is the file that came with Office 2000 (which is version 9)

The change screwed up a compiled VB6 program which now no longer runs.
I went back into MS Access as reselected the original file but the problem with my VB6 program, which also uses the Outlook Object Module, still exists.

This problem was definitely caused by this change.  The VB error I receive is Error Loading DLL.

I've performed an Access 2000 repair and it did not help.  Perhaps I need to re-register a DLL?  if so, which one??  

Please help!  Willing to try anything!
0
Hello,

Is there a way, in Excel, to merge multiple financial ledgers into a single summary ledger?

For example, suppose you have three checking accounts, each identified by the last four digits in its account number, and each with identical standard bank-account column headings as shown here:

2017-08-01a_EE.PNG
Furthermore, suppose that the ongoing transactions for each account are entered — either automatically or manually — into a worksheet according to account number. In other words, if the three account numbers are 5678, 4321, & 9753 and each has its own sheet tab (as shown above) then transactions, as they occur, are entered into their respective worksheet creating a separate and current register for each account.

With that information, how could you create a summary or "combined" worksheet which automatically displays transactions from the three individual accounts as they are entered?

In other words, all entries and edits would be made only in the single accounts but automatically appear in correct chronological order in the summary sheet.

By the way, my preference is to find a solution using only Excel formulas if that's possible, with VBA as the 2nd option.

Thanks
0
I am not able to delete  internet Explorer History Folders  on Week based . like i want to delete only 4 week ago folder. please help me
0
Hi,

I have this MSHFlexgrid1 col 2 that have different days in it with format "YYYY-MM-DD".

I would like to count the number of dates in this column falls on a Monday.

I tried with below code:
Private Sub cmdCount_VBMonday_Click()
    total1 = 0

    With MSHFlexGrid1

        For lngRow = 1 To .Row - 1

            If Weekday(Format(.TextMatrix(lngRow, 2), "yyyy-mm-dd")) = vbMonday Then
                total1 = total1 + 1
            End If

        Next

    End With
        
    txtVB_Monday_count = total1

End Sub

Open in new window


But if you try the sample project in attachment, i should get 2 records but it tells me ZERO.

How can i fix this?
Monday_date
Thanks for your help.
0

Visual Basic Classic

163K

Solutions

58K

Contributors

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.