VBA Help

Hello Team

I have a form which contains a tab control consisting of 7 pages. Within Page7 I have 9 checkboxes.

What I would like to achieve is to have code which on a save event will reference Page7 and loop through each checkbox to see if they have a true or false value.

if a true value is identified from any of the checkboxes then the code can flow through to completing the save event but if all 9 checkboxs each return a false value then the code will exist sub

Form Name: frmRenewalsAudit
Tab Control name: TabCtlCats
Checkbox Names: Chk1, Chk2 etc through to Chk9

Any assistance would be greatly appreciated.

Thank you in advance

Dale

5 Comments

Become a Microsoft Certified Solutions Expert

LVL 13

This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I am trying to use the Process object, but I can't seem to find the correct reference. When I compile, the "Dim Process As New ScriptUtilities.Process" statement causes a "User-defined type not defined" error. I am running Windows 7.

I cannot find the following: (1) a reference to "Script Utilities library," or (2) browse for "ScptUtl.DLL." The reference to "Microsoft scripting runtime" does not resolve the error. I'm using the following example:

https://www.motobit.com/help/scptutl/clu48.htm

Please hep resolve the error.

3 Comments

I have a daily workbook that starts out with five blank worksheets with the same 7 cells designated for data to be entered in each worksheet. As a sample is tested and data is entered in the 7 designated cells, the worksheet is renamed with the lot number of the sample.

I would like to add a summary worksheet that will contain the data from the five worksheets, and list the 7 values from each worksheet on a single row.

In the summary worksheet, is there a way to address the different worksheets even though the worksheet name has been changed from Sheet1 to LotNo123, Sheet2 to LotNo456, etc.? The lot number will always be something different. Also, on some days, there may only be 4 samples, and Sheet5 would not be renamed.

7 Comments

Hi!

I am trying to create a script that would send the selected email in either 2 PST Inbox and move them to specif project folder.

I have various PST files where each has an Inbox receiving emails.

I want to click on any email on any PST inbox and move them to their project folder.

The Projects folders are located under the Default PST Inbox:
PST File 1(All in inbox)
Inbox (256)
Projects DONE
Sivana LLC Project
Done tasks of Sivana
ToBeDeleted tasks of Sivana
Authorize First
Rurals Projects
Done tasks of Rurals
ToBeDeleted tasks of Rurals
Authorize First
PST FIles 2
Inbox (10)
Projects DONE
GNN LLC Project
Done tasks of GNN
ToBeDeleted tasks of GNN
Authorize First

Notice the in this example I have two inbox (2 PST) where the PST File 1 is the default PST. Also notices that both PST have up to each Inbox has 5 level of folders created under their respected Inbox.

What I am trying to do is at any Inbox click on the received email and send it to any folder of any PST.

I have search and worked on varios script, this is the closest I had found:

Sub aaaMoveIboxToFolderDEST()
 Dim OutApp As Outlook.Application
 Dim oNS As Outlook.NameSpace
 Dim objInboxFolder As

Open in new window

…

Outlook
VBA

14 Comments

After going through Ms Access VBA line by line to try and discover why my posting is not being recorded on the fake web site I have discovered a clear error on

 Set JSON = ParseJson(http.responseText)

Open in new window

See my full code here:

Private Sub CmdSales_Click()
  
'  Const SQL_SELECT As String = "SELECT * FROM Qry3;"
  Dim http As Object
  Dim JSON As Object
  Dim coll As VBA.Collection
  Dim dict As Scripting.Dictionary
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Set http = CreateObject("MSXML2.XMLHTTP")
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry4")
  http.Open "POST", "http://jsonplaceholder.typicode.com/comments, False"
  http.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
  http.setRequestHeader "Content-type", "application/Json"
  http.send ("ConvertToJson(coll, Whitespace:=3)")
  Set JSON = ParseJson(http.responseText)
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
  Set coll = New VBA.Collection
'  Set db = CurrentDb
'  Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      Set dict = New Scripting.Dictionary
      For Each fld In rs.Fields
        dict.Add fld.Name, rs.Fields(fld.Name).Value
      Next fld

      coll.Add dict
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set fld =

Open in new window

…

1 Comment

LVL 12

Question by

HuaMin Chen

3d agoSolved

Error in Excel file

Hi,
I don't know why there is "Type mismatch" error when I run 1st Macro (pressing Alt-F8), vs attached Excel file.
ZCOM-QRL-112N.xlsm

2 Comments

How do you write a Worksheet Selection Change to Hide rows before a start time and hide rows after the end time.
My schedule starts at row 5 Column D. It contains entries in 15 minute increments starting at 12:00am. and extends 11:45pm, row 101.
In B7 and B8 I have Start time Row: and End time Row, calculations. So I know (form example) that if the start time is 6:00am then that is row 30 and If stop time is 19:00, then that is row 81.
So what I would like to do is have a change event, that will (in the example above), Hide rows 29 through Row6, and Hide Rows 82 through Row 101.

This is variable because AB5 (work Start Time) and AC5 (work end time) can change, but the start time Row Count and End time Row count B7 and B8 will automatically update when AB5 and AC5 is changed. (I have already done that).

I hope this makes sense...
Thanks,
Norm

21 Comments

Hello experts,
I have the following procedure reported at:
https://www.experts-exchange.com/questions/29156896/

Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row

Open in new window

…

5 Comments

I have no problem when I send data from a recordset to an array using recordset.GetRows method in ADO, my problem comes when I try to make the same with DAO, the GetRows property only "pulls" the first record from the recordset, and I want to pull all the records from the recordset without using a loop.

I have tried the following with no success:

recordset.GetRows()

Open in new window

and

recordset.GetRows(.RecordCount)

Open in new window

Any guess?

5 Comments

Hello the experts :)

I'm working on a travel planning which is basically used by employees to fulfill the day's location then calculates different things like how much time they spent in each company's / client / supplier location and so on.

I'm tempted to merge & center some of those locations for easier reading.

May I expect some "serious" issues with formulas & vba or can I do that without worrying too much ?

Thank you very much & kind regards,

Magpie

3 Comments

HTML5 and CSS3 Fundamentals

LVL 13

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Hi
What Excel VBA could would I use to email the ActiveSheet as a PDF attachment using the Mail Envelope.
I very specifically need to use the mail envelope

3 Comments

I have a excel spreadsheet with two tabs, one tab (Bar Database) has a list of Locations and ID numbers. A:A is where the ID numbers sit and B:B is where the name of the location sits. The second tab comes from an online state database OData feed, the feed generates 100k+ results. Well in one of the columns is the same ID numbers from the list in Bar Database A:A. If it can filter just what is in the list it would only be 2900 rows (currently, next month it would be 2922, with an additional 22 results that I am pulling each month. )

I currently have the Power Query filtering the results down by manually searching and checking the box of the ID numbers I want, but a new location might pop up or one close and I figured it would just be easier to change the list on the spreadsheet then to edit the power query. Especially if someone from upper management uses it and just knows how to open excel.

3 Comments

Hi Experts,

What is the VBA Equivalent of the following Formula in Excel

=RIGHT(E2,FIND("_",E2,1)-1)

12 Comments

Hi Experts,

I'm having an issue with a macro in vba.

I have a "data" sheet in which we can find all the details related to task orders, such as ID, frequencies, categories, documentation, etc.

What I would like to have is a macro that creates a worsheet for every UNIQUE combination of Category and Procedure.

In each worksheet created, we would have all the related columns to that specific combination and that would act as a form to verify that everything has been done.

Is there any solution to this?

Thank you.
Formulaires_test2.xlsx

5 Comments

I need VBA macro assistance. I have an EXCEL workbook that i will be completing evaluations. Each evaluation will be its own worksheet. I want to have a user-form prompt that opens up when i open the workbook asking me how evaluations i want to complete. (please the example in the attached file). Once i click on the desired # of the evaluations, the worksheets should populate.

Example: If i select 4 from the dropdown list on that user-form, I want the EXCEL workbook to then insert 4 new tabs with the evaluation form in each of the tabs. Please see attached file.

SIDENOTE: The worksheet should have the same evaluation template (please see the attached file).
evaluation_example.xlsx

10 Comments

Hello experts,

I would like to add to my personal add-in a procedure that covers the following requirement:
1)      Display inputbox with the following message: “Please enter range in which you want to clear contents e.g: E2:G”. Warning your initial Range should contains a letter and a number.
2)      Clear content based on Range reported on activesheet:
a.      If range reported is “E2:G”, clear content should be applied as of Range E2 till G & last used range of activesheet

Checking process and error handling:
Þ      If user click on cancel on inputbox go to exit
Þ      If range reported is not properly reported go to exit with the following message: “Error with the range reported.”
If you have questions, please contact me.
Thank you for your help.

6 Comments

Hello,

Using Word 2010. Need code for inserting the document filename and path (c:\docs\document1.doc) on the left side and date and time on the other side of a footer but only on the last page of a document. Using 8-pt. Times New Roman.

Goal is to have a toolbar button for users. I know nothing about VBA.

I would like a solution simmilar to this post but with the addition of the date and time right justified:

https://www.experts-exchange.com/questions/22048617/VBA-macro-for-simple-last-page-footer.html

Thank you!

3 Comments

This seems simple, but I can't get it to work. At one point in my code, I want to open an existing Excel workbook (from a network location) so that the user of my toolbar can edit it directly and save changes.

I've tried a few different ways, but the workbook always opens in Read-only mode.

'Application.Workbooks.Open Filename:=supplierdatabase, ReadOnly:=False 'STILL OPENS READ ONLY
'Application.Workbooks.Open Filename:=supplierdatabase, ignorereadonlyrecommended:=True  'STILL OPENS READ ONLY
'Application.Workbooks.Open Filename:=supplierdatabase, ReadOnly:=False, ignorereadonlyrecommended:=True  'NO JOY
'SetAttr supplierdatabase, vbNormal 'tried resetting read-only attribute BEFORE opening but I get a FILE/PATH ACCESS ERROR

Open in new window

supplierdatabase is a valid path and file name string. File opens fine, but in read-only mode.

I also have full read-write rights to the network folder where this file lives, by the way.

Any suggestions?

5 Comments

how can I delete last element of an array in vba

2 Comments

Learn Ruby Fundamentals

LVL 13

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

Hello experts,

I am looking for a procedure to remove extra lines and extra spaces.
The idea is to:
1-Display inputbox to report column in which extra spaces and extra lines removal should be applied
2-Remove extra spaces and extra lines
I attached dummy file with input sheet and output sheet as expected.
Procedure should be applied in activesheet.
If you have questions, please contact me.
Dumm-file-extra-spaces-and-lines.xlsx

6 Comments

Hi Guys!

I am trying to create a VBA that will filter my data based on a data placed by a date-picker. The problem I have is,

When I click on the date desired, the control seems to stay on the picker (the month is left highlighted) - I am trying to take the control out of there

When clicked on the date desired, the control, like above, seems not active en ENTER or change of cell, so the VBA is not activated

Finally, if I change the date without the picker (me typing in the cell), the filter function seems to work but does not filter correctly the data based on the DATE.

Help please (excel attached)
Datepicker---Autofilter.xlsm

16 Comments

I have the following query which is working in MS Access 2003:

SELECT tLenSupplier.LSupplier, tLenSupplier.LastSub, tLenSupplier.LastSubResult FROM tLenSupplier WHERE (((tLenSupplier.LastSub)<>IsNull("LastSub"))) OR (((tLenSupplier.LastSubResult)<>IsNull("LastSubResult"))) ORDER BY tLenSupplier.LSupplier"

I would like to place this into a string to use in VBA for other purposes. I am having trouble converting the query into a string to set the variable "sSql" --> (Set rs = CurrentDb.OpenRecordset(sSql))

Any assistance is appreciated. Thank you.

11 Comments

Hello experts,
I have the following template.
I am looking for a procedure that covers the following requirement:
1)      Clear data of Update-spot-mstt-match sheet from Range A2 till last used range of F
2)      Delete rows of Update-spot-mstt-match sheet from last used range till row 2 (row 2 excluded)
3)      Copy from Mapping sheet Range A2 till last used range to Range C2 of Update-spot-mstt-match sheet
4)      Copy from Mapping Range B2 till last used range to Range A2 and B2 of Update-spot-mstt-match sheet
5)      Copy from Mapping sheet Range C2 till last used range to Range D2 and E2 of Update-spot-mstt-match sheet
6)      Copy from Mapping Range D2 till last used range to Range F2 of Update-spot-mstt-match sheet
I attached dummy file.
If you have questions please contact me.
Data-transferring-template.xlsm

10 Comments

I've got a client, who has an old Access 2003 database application that we are attempting to update to A2016 (32 bit) (latest version she has).

She has about 8 forms in the application which use the dbPix activeX control, but we are getting errors on properties and methods which are documented on the companies web page. I've checked the references and ensured that the ocx file has been added to the project.

I've replaced the dbPix control with an Access image control on one of the forms, which seems to work fine. I'm waiting for info from the client as to whether she wants to proceed with this change on all of the forms.

I have not dug into the code too deeply at this point, but as I understand it, one of the reasons for purchasing the software was to allow the users to connect a digital camera directly to their computer to capture and store the image.

1. Does anyone have any idea why the code behind these forms would be failing with "object doesn't support this property" and other property/method errors?

2. Has anybody used any other freeware that performs this same functionality?

9 Comments

An Expert here (Daniel ) excellently helped me in a script that made my task super easy (it's located here). The VBA extracts all the comments from a specific worksheet and lists them with links.

I am trying to modify the script so instead of placing the list in a separate worksheet, save the list in the same worksheet I am calling from to column U.

Help please!

7 Comments

VBA

Related Topics

VBA

Related Topics