VBA

12K

Solutions

4K

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

The idea is to create a script that removes rows where there are 2 or more numbers from different "decis". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from different decis" or something similar. If the input is one (1) then
2) choose the sheet to be processed
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

Remove rows containing 2 or more numbers from different decis
Notice how all the green rows contain number from only 1 "deci". That is the goal.
Remove-rows-with-2-or-more-numbers-.xlsb
0
I have a weird problem. 4 users access the same file on a shared drive, only 2 of the 4 have the MACRO listed under Macros in the same file. How can I find where this macro is located.
0
I had this question after viewing Random hours in timesheet.

As a complete newbie to VAB I followed the exact instructions provided in the quoted post. My problem is really really similar with a view exceptions. I need to work with Integers only and without specific percentages, rather Total hours per project as input values, which suppose to be randomly distributed across workdays respecting total hrs per day allowed by the employer on a specific day (example attached). So the bottom row is a manual input as well.
Timesheet Example
I started first to make the already proposed solution running. I made the following changes to the original script (disclaimer: working on Mac):
  • deleted all CDec() comands and replaced with simple natural numbers, since I need Integers only
  • replaced Scripting.Dictionary with another one since in VAB 2019 on Mac there is no "Microsoft Scripting Runtime". (Found a replacement also online that seems to work)

But having two issues:
While compiling a VAB Project there seem to be no mistakes or anything. But upon runtime in a single cell it says "Object doesn't support this property or method"
But in general I am assuming I am missing some ground knowledge. How do I run this macro across my cells? How do I provide input. What if I need to add 5 or 8 customers. Where do I input target hours per customer. Or how can I expand it to a whole month. So where is the input and output if I may say it like this.

Thanks in advance for any help.
0
The attached file contains a similar formula in cells Q21:Z27. the formula in Q21 is below. The formulas return numbers within a range.


=IFERROR(AGGREGATE(15,6,OFFSET($C$1:$G$1,1,0,46)/(OFFSET($C$1:$G$1,1,0,46)>P15),1),"")

I am looking for a way to use cell references instead. For instance, value in cell Q19 should replace G1 reference in formula and R19 should replace the second cell reference in the formula. Thus, when either value is changed the references within the formula are changed accordingly.

The Formulas in Q4:Z10 will serve as the original formula and should not be changed. Using a different formula that will have the same results is acceptable as long as the formula is not altered by the addition of rows above it or when it is copied and pasted.
ReturnValuesWithinRange.xlsb
0
Hello. Im requesting assistance with the following. I have a excel file that i want to filter according to the dropdown list selected. attached is the example file. In the "main" tab is where the table and the dropdown list will be featured. Once the table is filtered to the dropdown selection, the last column (column E on the "main" tab in the attachment) needs to be answered either with YES / NO. Once i have answered all the rows, I want excel to record the options selected in the "results" tab.

the "results" tab will have the same columns as 'main" tab.

Your assistance is greatly appreciated.
work_deck.xlsx
0
Hi Experts,
 I have one Excel file in one folder (C:/Source). I have also on Macro enable excel file where i need to copy source data to Macro enabled file with formatting. I need your help to write Macro to get file from Source folder and paste to Output tab.    I am attaching Input and Report file with sample data,

Can you please help me?

Source File

Input
Desired Output
OutputInput-FIle.xlsx
Report.xlsm
0
With VBA in Word, find distance between top of document and cursor location.
0
My problem is that I don't know how to reference a closed workbook when the path dynamically changes but the file, worksheet, and cell location are consistent.  A hard-coded example of what I'm trying to do is here:

Sheet4.Cells(1, 22) = "= 'P:\Laser\Service Reports\L2100\CT&R\" & "[Collect Tool & result.xlsm]Report'!B4" 'this works when path is hard-coded

HOWEVER...my path will change.  I know what the path is through a variable that I define prior to the line of code above.  The path location will be known with variable strFolder.  Put simply, I'm looking to do something like below, but it errors out.

Sheet4.Cells(2, 22) = strFolder & "[Collect Tool & result.xlsm]Report'!B4"

Is there a method to do this?

Thank You in advance
0
Hi

What Access VBA code would I use to generate a list of all the control names on all forms in Access and export this list to Excel

Thanks
0
I have an excel file with Name email address and html file location.
I need to automate this process.
i.e. Send email based on the email address and html content in the email.
GTMarketing.xlsx
0
Hello Experts,
I have a form with two separate sub-forms

Main Form: f-Project
SubForm1:f-ProjectAdd
SubForm2: f-ProjectHours

I have a "Save Record" button on f-ProjectAdd.  After this button is clicked, I need for that new record to appear in the combobox on f-ProjectHours.

Can you please provide me with the VBA code to: 1) save the record from f-ProjectAdd, and 2) refresh/requery the combobox on f-ProjectHours.

Thanks,
Ken
0
I have an excel file with a button to setup a document to print - it auto-hides some columns, reformats others, and then brings up the Print Preview window.  Then, it saves a record of the data to an Access database

SOMETIMES, I realize I needed to do something else and instead of completing the print, I cancel.  (Right now, when cancelled, it still logs in access and I want to stop that).  But short of prompting the user to save the data to access with a MsgBox, is there any way to know that the user did not (or, more likely, DID) press the print button to complete the print job?  Then I would kick off the access logging.

For example, something like:

If PrintCompleted = True Then
   LogToAccess 'sub that handles access logging.
End If

Just to be clear, I don't need feedback from the printer to know that the page actually came out.  I just need to know that the printer got the job.
0
Hi

I am importing images into cells, and I need them to move from their default top left position to the centre of the cell.

They are all in the same column,

Hope someone can help!

Thanks

Paul
0
VBA code for Office 365

Refresh table Links  

Does anyone have a code example of how to refresh Links in Office  365.
Links that are going to sql server  "work_flow"  is my ODBC connection database ?

Thanks
fordraiders
0
How do I assign a keyboard shortcut to an existing MS Word Macro?
0
I have code to create an email, but I don't know how to copy a range in excel to the body of the email.  I need to add range "A14:G14" and all of the additional rows with data in column "A".  I have formulas in 2 of the other columns.
0
Hi,

I am receiving a Write Conflict on my data entry in Ms-Access.
I am using Ms-Access as a front end, Mysql as a back end.

This Write Conflic msg appears even when one single user is using the application.  

Please see the attached picture.

Please advise solution.
Thank you.
Write-Conflict.png
0
Hi,
I am in the process of making appear my database more professional.
I am working the GUI.

Please find attached a picture of the Invoice.
Please give me your opinion on the design based on your experience on user interface, like Colours, Menus, balance, Icons, and any other element design that you can suggest to remove, add or edit...

Feel free to give your opinions.

Thank you.
Invoice-Layout.png
0
Hi,

I was surfing on the web and found a menu I like to recreate in my database.   Please see attached picture, in the Red Circle.

How can I do that?  I mean:
1. Fix the form to the left side and also have the other forms open (No pop ups forms).
2. Create that Tree effect or style, where I can click and open forms from there?

Thank you,
0
When I open and read emails in Outlook O365 (desktop) with zoom set to 100%, it's too small. I'd like the zoom to be permanently set to 120%. Currently it's 100% and apparently resets back to 100% even after I change it. It's too small and the text is difficult to read.
I'd like VBA code so that whenever I enter Outlook, my magnification is set to 120%.
Whenever I open an email, my magnification is set to 120%
0
So here's the issue.
We have two tables.  One table is called tblTests and the other is called tblImplemented
tblTest has five fields.  ID, ImpCase, TestIdentifier, TestResult, and TestStatus
tblImplemented has four fields.  ID, ImpCase, Description, ImpStatus

They are not joined.

tblImplemented has 512 Case records.  
tblTest has 1619 Tests that need to be ran.

tblImplemented has the ImpCase from DC1 to DC512
In the tblTest, ImpCase for DC1 can have 5 Testidentifiers where as ImpCase for DC2 has 2 TestIdentifiers. (The number varies from one test to another)

If the test result for any TestIdentifier in tblTest that has a ImpID of say DC1= "Not Completed" then the ImpStatus in tblImplemented should  = "Not Compliant."

I have tried using an Update query but when I add my two tables and join them via the ImpCase I get a number that far exceeds the amount of records I could possibly have.

All I want to happen is if any status say for DC1 = "Not Completed" then the Case changes to "Not Compliant"  So if out of the five TestIdentifiers one of them says Not Completed it will change the Case to "Not Compliant"  It's an all or nothing thing.

Can this be done or do I need to redesign the tables to read as a one to many relationship.  Which would entail a lot of work.

Thanks
John
0
Does anyone know of a VB6 addin that can replicate Notepad++'s ability to highlight selected text?

When I have a code window open and I want to see where I've used a variable, I'd for the vb6 IDE's window to highlight the selected text like this:
VBHighlighting.pngThe lighter highlighting is not important - I just want to be able to highlight one block of text and have everything in that code window that matches the block (case sensitive) get highlighted as well.

Anyone know how this can be done - I doubt the built in IDE for VB6 supports this, but hoping someone wrote an add-in... Rather it were free but would consider paying...
0
I would like to create a search folder in outlook that would count the unread emails in all subfolders except the junk and deleted folders in the inbox.I am having trouble assigning the criteria to the add search folder
0
Hello Team

Following below is the code which I have written to trigger and generate emails within access along with PDF attachments.

The issue have is,,  sometimes when the users choose not the send the email and cancel the dispatch of it, the send action does get cancelled but when the email closes and focus returns to Access, the application freezes and the user cannot release Access from it's frozen state and then has to carryout a Ctl Alt Delete action.


Private Sub cmdSendEmail_Click()



If Me.Dirty Then

MsgBox "Please save audit record before proceeding with sending email.", vbExclamation, "Save Audit Alert"

Exit Sub

Else


On Error GoTo Send_Click_Err

    DoCmd.SendObject acReport, "RptNewBusnFeedbackV2", "PDFFormat(*.pdf)", Form_FrmNewBusAudit_Bus.RepEmail.Value, Form_FrmNewBusAudit_Bus.ManagerEmail.Value, "", "Plan Audit, New Business Feedback Form" & " - " & Form_FrmNewBusAudit_Bus.User.Column(1) & " - Audit ID: " & Form_FrmNewBusAudit_Bus.ID.Value & " - Group: " & Form_FrmNewBusAudit_Bus.GroupName.Value & "   " & Form_FrmNewBusAudit_Bus.GroupNumber.Value, "", True, """"

   

    Me.txtEmailSentDate.Enabled = True
      Me.txtEmailSentDate.Value = Now
    Forms!FrmNewBusAudit_Bus!txtEmailSentDate.SetFocus

End If

Send_Click_Exit:
    Exit Sub

Send_Click_Err:
    MsgBox Error$
    Resume Send_Click_Exit

End Sub

Open in new window



As mentioned, the error doesn't always occur and as yet I have not been able to see a pattern that highlights the possible reasons of why it occurs.  

Just for reference, the code is executed with the Citrix operating environment.

Any advise provided will be must appreciated.

Thanks in advance.

Dale
0
Hello experts,

The following procedure allows me to add tick character on a range based on double click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LastRow As Long
    
    LastRow = ActiveSheet.Cells(Rows.Count, ActiveColumn).End(xlUp).Row
    
    If Not Intersect(Target, Range("D2:H" & LastRow)) Is Nothing Then
        Application.EnableEvents = False
        If ActiveCell.Value = ChrW(&H2713) Then
            ActiveCell.ClearContents
        Else
            ActiveCell.Value = ChrW(&H2713)
        End If
        Cancel = True
    End If
    Application.EnableEvents = True
End Sub

Open in new window


I would like to add the following requirement:
1-Instead of adding a single tick, I would like to add an inputbox after double click with the following message: "How many ticks do you want to add" Based on the answer tick reported should be added.
Example: If user report 4, 4 ticks should be added. If user doesn't report a numeric value, exit sub with the following message. "Unable to proceed as value reported is not numeric."

If you have questions, please contact me.

I attached dummy file.

Thank you for your help.
Dummy-file-ticks.xlsm
0

VBA

12K

Solutions

4K

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.