VBA

6K

Solutions

22

Articles & Videos

2K

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

Hello!

I have a database (attached) that allows for bid submittals to be created. Right now, the macro Add Work Item allows for a new worksheet to be created and it takes the user to the worksheet afterwards. This is great, the issue is that right now the code is set up to make the work item one number larger than the one previous to it in the table. However, this is not going to be the case for the user as work items can really be any number.

Is there a way that I could create a prompt that allows the user to enter in what they would like the work number to be and then once they enter in that work item number it takes them to the new worksheet, with the worksheet named after whatever the user entered as the work item? Additionally, the hyperlink feature in the Jump To column would still need to be the same number as whatever the user entered as the work item number.

Thank you so much!! Any help is greatly appreciated.
editing-EstimatingSpreadsheet.xlsm
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.

So, there can be more companies/adgroups like Sales Pvt ltd and Apparel Pvt ltd. Then there will be many columns hike G,H or each adgroup and then in column B and C only the Adgroup with highest % score for the new keyword and the corresponding score will be displayed. So, if this whole calculation and comparison of the scores for adgroups for each new keyword can be coded so that for each new keyword when entered in A it will show the highest scored adgroup and its score then it will be perfect solution for me.

In the attached file I have hidden the column  F to H as it should be coded in backend/made dynamic for the output needed to be generated in column B and C with repecct to each new keyword in column A.
29040844_mod.xlsm
0
Hello!

I have a database (attached) that helps to create bid submittals. When the Add Work Item button is clicked a macro is run that allows for a new worksheet to be created and this new worksheet is based off the template. However, I am having some issues with the hyperlinking between the created worksheet and Table1 on the summary sheet. All of the prices are not connecting properly and are just copied from the previous row instead of connecting to the created worksheet. The prices should just be left blank until the user enters in the values in the worksheet that they are brought to when they created a new item.

Here are the following connections that must be made between Table1 and the created worksheet based off the template.

- column E connected to D38
- column F to F34
- column G to F35
- column H to F36
- column I to F40
- column J to D1

the previous code I was using for this was

        WS.Cells(I, "E").Formula = "=" & sWItem & "!D38"
        WS.Cells(I, "F").Formula = "=" & sWItem & "!F34"
        WS.Cells(I, "G").Formula = "=" & sWItem & "!F35"
        WS.Cells(I, "H").Formula = "=" & sWItem & "!F36"
        WS.Cells(I, "I").Formula = "=" & sWItem & "!F40"
        WS.Cells(I, "J").Formula = "=" & sWItem & "!D1"

But now the worksheet variables are defined differently and I cannot seem to switch them to work properly.

Thank you so much! Please let me know if I need to be more clear.
editing-EstimatingSpreadsheet.xlsm
0
Hi

I am a newby, however I am progressing with a treeview project. My latest problem is;

3061 Too few parameters. Expected 1

This comes up after stepping through the following;

    Dim rsReqs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
   
   
    strSQL = "SELECT dbo_BomHeaders.ID, dbo_BomHeaders.BomReference, dbo_BomHeaders.Description " _
    & "FROM dbo_BomHeaders " _
    & "WHERE (((dbo_BomHeaders.ID)=[Forms]![frm_Treeview_Example]![Combo0])); "
   
    'Debug.Print strSQL

    Set db = CurrentDb
    Set rsReqs = db.OpenRecordset(strSQL)   (3061 appears at this point)

The SQL string is copied from the query (criteria) which is the record source for the form on which the combo box resides. I have also implemented Alan Brownes 'Return to the same record next time form is opened' so that the combo box is populated on the form opening.
0
I've researched this online, finding mostly solutions using google translate, and haven't been able to get anything to work yet. First off, I'm wondering if there's a way to automate the built in Microsoft translator which is so easy to use manually. Is there a way to do that with VBA?

Thanks,
John
0
Deleteaftersubmit = True

Sir, Even after doing this.
Mail is seen in the deleted items folder.

In case, i want it to be deleted from DELETED ITEMS Folder as well.

Then, can we do it using VBA.
0
There is a addition / the final output that I am looking for which should be easy for you to add since 80% of the code is ready.  So, there will be another Tab, for finding right companies to map for a Keyword. where I will add various keywords and the code will calculate the right company based on highest % sum of the words matching for a company. I have added a 3rd Tab in the attached worksheet which shows the intended output/calculation logic.  If you can help me with it then this will make my day.

Thank you so much again for all your help and time !!!
29039630a-_-Final-Addition.xlsm
0
Hi Guys, within an Excel macro, I use the following code which filters Column 12 for a Range called "PrevDay" (which is a Cell which has the Previous Day's Date) but it does not work. What's wrong with the code?

Sheets("PL Star Accrual").Select


    Rows("26:26").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$26:$AZ$242").AutoFilter Field:=15, Criteria1:="0"

    ActiveSheet.Range("$A$26:$AZ$242").AutoFilter Field:=12, Criteria1:=Range("Prevday")
0
Hi Experts,

I realized that MSFT changed the way it was handling combo boxes that were not selected any values, up till version 2007 the it was considered null, and the function IsNull(myComboBox) returned true, while IsEmpty(myComboBox) returned false, and from version 2010 its the opposite, Isnull is false and IsEmpty is true.

Now I'm used to have nz(MyComboBox,FillInValue) working fine and now that does return the FillInValue anymore, I guess its due to the above.

Now my question.
Did you also experienced that behavior?
What is the alternative to the NZ function? (Besides for having to use the IIF() function which will make things slower IMO).

Thanks in advance.
0
I've been trying to get the total from a column to a textbox, but I can't get the form to open when use the same code from my other worksheet.  schedules.xlsm
0
[Webinar] Learn How Hackers Steal Your Credentials
LVL 9
[Webinar] Learn 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

Good day,
I have an excel file at 407KB and around 2,000 rows. I'm trying to filter the information to copy it to a different sheet within the workbook. however, when I try I always get a prompt saying excel is out of memory. Also, when I copy the entire data without filters, it works fine. Is there any way to fix this?
I am running Office Pro 2016 64bit, 8GB RAM, Win 10. Thanks.
0
I have an Excel spreadsheet that has a training date and in the column next to it, how many people are to be trained on that day.
What cell formula or VBA would I use to print  a roster with that date on that many rows of the roster? For example, if a certain class on July 13 has 25 students, how could I have code that would print a roster with 25 rows, each row with the date and a line beside it for a person to sign in?
Training-Dates.xlsx
0
Hello,

I have a database (attached) that allows for the cost of bids to be calculated. Right now, the button Generate Work Items allows for new sheets to be created based off the amount of rows in the Work Item column in the summary sheet. These new sheets are based off of a template and are linked to the summary page as well as the rate sheet.

However, I now want to change the purpose of this Generate Work Items button to something different .. I now need the button to instead just create a new row in the summary table and a new worksheet for that new row. But still having that new worksheet linked to the summary table, template, and rates sheets as it is now in the macro. Also when the new row is created the total at the bottom of the table should also move down a row. And when the button is clicked to create a new worksheet it would be great if excel could take the user to the new sheet directly afterwards.

For the name of new sheet, it should be the Work Item number from column B in the summary sheet, but the user will not have input that yet when creating the new sheet.. (Right now the work item number in column B is connected to cell B3 on the template sheet that the new worksheets are based off of) Maybe when the user clicks the Generate Worksheets button a prompt could come up that asks the user for the work item number and then the sheet would be created and whatever the user entered as the work item would be the name of the sheet?

Thanks so much, please…
0
Hello,

This is another in a series of questions I have posted recently to come up with Excel VBA code that will selectively reformat a specified string (or strings) of text within a cell, but not the entire cell.

For example, suppose you wanted to reformat a sentence in cell B2 to that shown in D2:

2017-06-27a_EE.PNG
Note that the particular formats shown in this screenshot are arbitrary as the main objective is to have a code which can apply any desired formatting.

Thanks
0
Word 2013; VBA. I have a series of macros that I would like to use on another computer. (All the macros are initiated by a form.)  All I need to do is initiate the form and I am off and running.

I cannot change the other computer's normal.dotm file. So I put the macros in another dotm file (I'll call it mydotm.dotm), which I can make available on the other computer.

On that other computer, I open mydotm.dotm. I can see my macros there. In another window, I open the document on which I wish to use the macros. But from there I cannot see (View/Macros) the macros that are in my mydotm.dotm file. All I get is a blank slate.

I'm stuck.

So I thought if I started with the mtdotm.dotm file active (and the other file open in another window), initiated the startup macro from there, had the macro first switch to the other document, then I could continue with the macro execution there.

As the first statement in the startup macro, I used      ActiveWindow.Next.Activate to switch to the other document. That blows up with an error 91 - "Object variable or With block variable not set" message.

Huh?

Am I taking the wrong approach, or is my switch document command wrong?

Can you point me in the right direction, please?

Thank you.
0
i have a column of data that needs to be replaced from the left. the string that needs to be replaced has an inconsistent length due to a set of digits (8-15 in length) but it has a definite stopping point at an open parenthesis.

I thought something like this would work
Columns("C:C").Select
Selection.Replace What:= _
        Left(Selection.Value, InStr(1, Selection.Value, "(")) _
         , Replacement:= _
        "", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

but it doesn't. I suspect because my selection is the entire column. any help would be appreciated.
0
Hi -
I have an app that was written in 2022 in 32 bit. I need now to revisit it and migrate to 64 bit. I am getting the error:

Screen-Shot-2017-06-27-at-14.43.59.png
can anyone help?
thanks in advance
joao
0
Dear Experts:

I would run a macro that performs the following actions:

If row 1 and row 2 (both conditions have to be met at the same time) have entries, copy the second row and place it right below the second row so that 3 contiguous rows result.

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

Regards, Andreas
0
Tables are related 1 to many in a structure:
Parent Folder
       Sub-folder
             Files

Parent Folder holds the Main starting Folder Name
       Sub-Folder holds all of the names of the individual folders in this first layer
              Files holds all of the names of each file contained in each sub-folder.

Can this be done using VBA ?

Example Data:

Parent Folder:  2017-06-29
      Subfolders:  1, 2, 3, 4, 5, 6, 7, etc (always numerical)
            Files:  (In Subfolder 1: 01.pdf, 02.pdf, o3.pdf)

Or, do I just record the entire details of the structure and then break it out with an action query ?

Thanks,

Bill
0
Enroll in June's Course of the Month
LVL 9
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

I have a code that creates a code in a module, but when it does the VBA window flashes up while the code is running.
I have tried 'Application.screenupdating = false', and it does not work, I have also tried    'ThisWorkbook.VBProject.VBE.MainWindow.Visible = False' at the beginning and end of the code but the window still pops up for a short time. I would like to run the code without the vba window being visable at all.

this is the code:



Sub code
   
        Set vbp = Application.VBE.ActiveVBProject
    Set vbc = vbp.VBComponents.add(vbext_ct_StdModule)
    vbc.Name = "CreateButton"
    strCode = "Sub CreateButtons()" & vbNewLine & _
    "ActiveSheet.Buttons.Add(840, 10, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""ClaimsLog""" & vbNewLine & _
    "Selection.Characters.Text = ""Claims Log"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 40, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""UNM23Report""" & vbNewLine & _
    "Selection.Characters.Text = ""UNM23 Report"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 70, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""SPLEtool""" & vbNewLine & _
    …
0
I'm building a Microsoft Access application with a login authentication feature, I wanted to avoid the need for users to enter a login and password each time they need the use the app and then thought of using biometrics . How can I implement the use of a biometric login to a Microsoft Access application.
0
Hi Experts. I was able to source this VBA code from a question answered but I need to modify it slightly. I have X number of workbooks with Y number of worksheets in each book. I have information that I would like to compile onto a single master data workbook. The code below is able to gather the data from the first sheet from all the selected workbooks. I need it to do the same for the remaining worksheets. Please help me with this request! Would really appreciate the help. :)

Sub MergeTest()

    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim LastRow As Long

    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Open the file dialog box and filter on Excel files, allowing multiple files
    ' to be selected.
    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)

        ' Open the …
0
I was sent a spreadsheet (outputted by a source system that I do not know its name) and the value shows 3.2621E+107

The value should read 32621E103.  I have searched an hour on the Web.  I have tried paste special to text, I have tried formatting as @ and 0. I even tried Text to Columns.   I have tried everything that has been suggested and nothing works.  How can I display the correct value.
0
Getting a Type Mismatch error in excel vba when trying to open a form.schedules.xlsm
0
have the tool woking but need
have the selecte in yellow  in sheet ToSEE

te ture be in yello as selected in like here
ooooooooooooooooooooooooooooo.png29040080a--1-V01--3-.xlsm
0

VBA

6K

Solutions

22

Articles & Videos

2K

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.