VBA

11K

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

count from last to top

in column LM

as show

a12.PNG

file
29143233_to_base_show.xlsm
0
Learn Ruby Fundamentals
LVL 13
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I have some field headers in a work sheet that are in Row2.  What I need to do is, no matter what row/column ActiveCell is, I need to know what the header value is in Row2.  So if ActiveCell is R20, I need to know what is the value in R2, and set it to a variable like "HeaderNameOfActiveCell".  If ActiveCell is Q100, the HeaderNameOfActiveCell = [Whatever value is in Q2], and so on.  Even if HeaderNameOfActiveCell is blank.

In the first record that was easy which is Row 3, I used: HeaderNameOfActiveCell = ActiveCell.Offset(-1,0).Value, but because the randomness of rules that can select the ActiveCell, it could, skip a few rows until it finds another cell to act on, so that sort of ruled out using For Loop.  I was hoping to be able to replace the -1 with something that could name HeaderNameOfActiveCell?

Looking for ideas on how I can grab the header value, and save it as a variable, no matter where the ActiveCell is.  Please advise and thanks.
0
Greetings experts,

I need help creating a code that will find and delete embedded objects.

As of now I have found this:
  ActiveSheet.OLEObjects.Delete

Open in new window


However, it also deletes all my other buttons, shapes and pictures..
I only need it to embedded objects like this:
1.JPG
Any help is much appreciated! :)
0
matchthe numeber from column BT to shoe the 2 digit or 3 digit from column AY

a7.PNG29142835--2-_to_match_the_key_num.xlsm
0
Hi Experts

I am trying to encrypt data from a field with filed type longtext in Microsoft Access throug VBA.  I have downloaded an AES encryption script from Rabbit.  Here is the code - I have no idea of how to implement it.  The text input filed is called Text1 and I am trying to output it to Text3.  Your help will be much appreciated

Option Compare Database
' Dependency on "functions Base64" and "functions SHA1"
Function StoreEncryptAES(sMessage As String, sPassword As String, Optional iRounds As Integer = 20000) As String
    StoreEncryptAES = Encode64(EncryptAES(SHA1HASH(sMessage) & ":" & sMessage, sPassword, iRounds))
End Function

' Dependency on "functions Base64" and "functions SHA1"
Function RetrieveDecryptAES(sMessage As String, sPassword As String, Optional iRounds As Integer = 20000, Optional asString As Boolean = False) As String
    Dim dMessage As String
    Dim hash As String
    Dim separator As Integer
    dMessage = DecryptAES(Decode64(sMessage), sPassword, iRounds)
    If InStr(dMessage, ":") < 1 Then
        If asString = True Then
            RetrieveDecryptAES = "#invalidpassword"
        Else
            Err.Raise 640, "functions AES", "Invalid password/passphrase"
        End If
    End If
    hash = Left(dMessage, InStr(dMessage, ":") - 1)
    RetrieveDecryptAES = Right(dMessage, Len(dMessage) - (Len(hash) + 1))
    If hash <> SHA1HASH(RetrieveDecryptAES) Then
        If asString = True Then
            RetrieveDecryptAES = 

Open in new window

0
gurus,

could you please, if there is a way in excel as attached ,
i have four tabs , where i add
1. List
2. Submitted
3. Pending
4. Won

List page contains data
where i select submitted , i want that row to have a copy in submitted sheet automated
similary for Pending and Won .

is this  acheivable

BR,
SiD
Eg1.xlsx
0
Greetings experts,

I have a very weird problem...
I have a code embedded in "This Worksheet" and what it does is that it will reset Option Button 1 and 2 and hide some cells whenever the the workbook is re-opened. I also have a button that will show and hide another range of certain cells. If I press any option button, hide the cells and save, when I reopen, all the objects in the hidden range are deleted.
Capture.JPG
Attached here is an excel file for your reference: Book1.xlsm

Any help is much appreciated! :)
0
Command Line from VBA

From VBA, I need to run Command Line (Shell) commands.

For Example in Command Line I would type:

 Del C:\......\Folder1\*.txt

How would I do that from VBA?  All the Shell etc... commands I try are not working.

Help.

Thanks
0
Hi,

Please note, I am looking for MS Access VBA script to rename an excel file as below.

C:\folder 1\ abcd.xlsx to
C:\folder 1\ abcd_16April2019.xlsx (changing the file name with today date)

Thank you
A
0
Hi

I want to use Excel VBA to copy everything from a specific sheet called "data" into a new Excel csv (Comma Delimited) file.
How would I do this?
0
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Hi
I am using the following Excel VBA code to pull data from a SQL database. It doesn't pull the column headers.
How do I change it to include headers? Thanks

            Set objMyConn = New ADODB.Connection
            Set objMyRecordset = New ADODB.Recordset
         
            Dim S As String
    
        'Open Connection'
            objMyConn.ConnectionString = "xxxxxx"
            objMyConn.Open
    
    
        'Open Recordset'
            Set objMyRecordset.ActiveConnection = objMyConn
            objMyRecordset.Open strSQL
    
        'Copy Data to Excel'
            ActiveSheet.Range(oRangeStart).CopyFromRecordset (objMyRecordset)
            

Open in new window

0
Trying to get an AutoHotKey script for copying from Excel.

Seems like the answer is in this link.

https://autohotkey.com/board/topic/62759-remove-linebreak-after-copying-excel/

Where it resolved to just a single line of code in the end after about twenty iterations.

If you are just looking to trim the line feed off the clipboard content (like what you get after copying excel)

StringTrimRight, MyVar1, clipboard, 2

should store your string to MyVar1

However, don't know what the whole script should look like.

Sincerely,

OT
0
Greetings experts,

I have two option buttons, option button 114 and 115 on a sheet.

I need help creating a code to put into Private Sub Worksheet_Change(ByVal Target As Range)

The requirements:
1. If both option button 114 and 115 are left unselected, row 12, 14 and 16 are left hidden
2. If option button 114 is selected, row 12 and 14 appears and  row 16 is left hidden
3. If option button 115 is selected, row 16 appears and  row 12 are left hidden


Any help is much appreciated! :)
1
I need to count cells in a range that are colored with conditional formatting. I used UDF that counts cells in a range that are manually colored but could not find te right function that does the same thing for those cells that are colored with CF.  Could someone give me a  sample code to achieve this please?
0
I'm using the below code to send Outlook emails from MS Access. I would like to encrypt the email using vba ... maybe enable the encryption button on the ribbon.

    Dim sMail                 As String
    Dim objOutlook            As Object
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim strName               As String
    Dim sSubj                 As String
    Dim sBody                 As String
    Dim sTo                   As String
    Dim strCC                 As String
    Dim sCC                   As String
   
   
  

    'Get Email Addres
    sSQL = vbNullString
    sSQL = sSQL & "SELECT   tblTempShipNotice.Email," & vbCrLf
    sSQL = sSQL & "         tblTempShipNotice.Lname," & vbCrLf
    sSQL = sSQL & "         Concatenate(""SELECT Emails FROM tblFO WHERE FO='"" & Nz([FO],""~999"") & ""';"","";"") AS CCEmails," & vbCrLf
    sSQL = sSQL & "         tblTempShipNotice.Adjudicator" & vbCrLf
    sSQL = sSQL & "FROM tblTempShipNotice;"

    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Set objOutlook = CreateObject("Outlook.Application")
            Do While Not .EOF
                'Initialize the variable for each loop
                strName = ""
                sMail = ""
                sSubj = ""
                
                'Populate the variables with our record data
                strName = Nz(rs![Adjudicator], "")
                

Open in new window

0
Hi

In Excel VBA (2013, 2016 or 2019) to add a button to the quick access toolbar when you open a workbook that will run a procedure when clicked?
0
will need create an 3 digit and 2 digit
show in columns LJ  and LL
a3.PNG
file
29142786a_to_LJ-and-LL.xlsm
0
Gurus,

i have a scenario where i have two sheets in excel , Sheet 1 and Sheet 2 .
i would like to know, if
1. if i enter data in Sheet 1 , it should automatically be entered in Sheet 2
2. whereas in Sheet 2 , i need to enter data manually that shouldn't go to sheet 1

is there a way to achieve in excel , kindly advise howto

BR,
Sid
Eg1.xlsx
0
need count each digit from column EG

a1.PNG
results goes to column LG , LH, LI

a3.PNG
file
3N_to_count_pair_digit.xlsm
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hi, I have an excel 2003 file (on windows xp) which imports data from a website via some vba code (originally provided by a very helpful expert on this forum). It did work before, but then stopped working so I am trying to resurrect it.   The result is a leaderboard from various golf tournaments (it's that time of year!). For each new event, I update the url to pull the most recent table. The attached sample file has the details - the macro shows the code which I am using.  Thank you for any help.
EE_sample_20190412.xls
0
calculate no of days based on hours for input
my group used to give me this  varaible in DAYS..now its in Hours

NoOfDays = Me.cboACTIVITY_TASK.Column(5)  i.e.  now its  "48"  meaning 48 hours.  


I basically now need to calculate the due date from todays  date and add 48 hours to todays date...and placed that due date in
Me.EXPECTED_COMPLETED_DATE = CountDays



' Function to count no of working days
Dim tmpNo As Long
Dim tmpDate As Date
Dim tmpStartDate As Date
Dim i As Long
Dim CountDays As Date
Dim NoOfDays As Long
' get the days from task chosen
On Error Resume Next
NoOfDays = Me.cboACTIVITY_TASK.Column(5)
If NoOfDays = 0 Then Exit Sub
If NoOfDays = Null Then Exit Sub
tmpNo = NoOfDays


'Me.txtColumnId = Me.cboACTIVITY_TASK.Column(0)


' get the start date from current date
startDate = Date
tmpStartDate = startDate
tmpDate = startDate

i = 0

Do Until i = NoOfDays

If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
tmpNo = tmpNo + 1
Else
i = i + 1
End If
tmpDate = tmpDate + 1
Loop

CountDays = DateAdd("d", tmpNo, tmpStartDate)

Me.EXPECTED_COMPLETED_DATE = CountDays

Open in new window



Thanks
fordraiders
0
I created a Button in Access to open another form with the same record selected.
(The wizard created a Macro which worked fine).

I then "converted the Form's Macro to VBA "
The conversion produced faulty VBA (Expected end of statemend of statement Error)
Docmd.OpenForm "VOCAB ENTRY", acNormal, "", "[THEWORD]=" &" '" [&TextTHEWORD] & "'", , acNormal

Open in new window

0
Hi
I have an Excel spreadsheet populated with data only in column A. The number of rows vary.
Each cell in column A (A1, A2, ...) is filled with a text that starts with a number appended with a text string.
Eg: A1 = "102heaven"; A2 = "56flower"....
I need a macro that will put the number in column B and the text in column C
eg:
A1="102heaven" --> B1 = 102 and C1 = "heaven"
Thanks
0
In my excel worksheet, I have a cell with complicated formula, and rather then use references like "A2" I thought it neat to use a listobject with the naming benefits it gives me.
You can see the formula below:
=SUMIFS(tbl_sales_report[AB 2019];tbl_sales_report[Business Function Key];B13;.....)

Open in new window

There are some more arguments in it, several references.

Now the datasheet that contains tbl_sales_report is populated by an addin outside of my control. This addin deletes the range, and inserts the data.
My code then re-creates the table. However, after this step of range deletion, and table recreation, my pretty formular now simply says:
=SUMIFS(#REF!;#REF!;B13;#REF!;D13;#REF!;F13;#REF!;H13;#REF!;J13;#REF!;L13;#REF!;N13;#REF!;">=" & P13;#REF!;"<=" & Q13)

Open in new window


Even though after re-creation all the references are good. Is there any way I can tell excel keep its grubby hands off my formula?

I have already tried:
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
which made no difference.
0
Greetings experts,

I have this code which I populate some cells and it works for the AutoPopulateExistingVendorCode but not for the AutoPopulateAvailability. For the AutoPopulateAvailability, I am trying to populate cell D20 based on cell D18, the value in cell D18 will exist in another sheet called "IO Number" and cell D20 will be populated based on cell D18's column G value in this sheet.

Private Sub Worksheet_Change(ByVal Target As Range)





If Target.Count > 1 Then Exit Sub

If Target.Address <> [D10].Address Then Exit Sub

If Target = "" Then Exit Sub

AutoPopulateExistingVendorCode



If Target.Count > 1 Then Exit Sub

If Target.Address <> [D18].Address Then Exit Sub

If Target = "" Then Exit Sub

AutoPopulateAvailability



If Range("D16").Value > 1000 Then

MsgBox ("Please request a new vendor code from ESP before continueing")

End If



End Sub



Sub AutoPopulateExistingVendorCode()

Dim rng As Range

Set rng = Sheets("Vendor Code List").Columns("f").Find([D10], lookat:=xlWhole)

If Not rng Is Nothing Then [D12] = rng.Offset(, -1)

End Sub



Sub AutoPopulateAvailability()

Dim rng As Range

Set rng = Sheets("IO Number").Columns("a").Find([D18], lookat:=xlWhole)

If Not rng Is Nothing Then [D20] = rng.Offset(, 5)

End Sub

Open in new window


Attached here is an example file: example.xlsm

Any help is much appreciated! :)
0

VBA

11K

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.