1. S

    How to create a Boolean strings in excel with VBA Macro

    Hi Friends, I tried with a formula creating Boolean strings but its time consuming every time. This is an example: In Column, AI have list of words from A1:A50, It varies every time from A1:A50. ColumnB AND words ColumnC NOT words Here I want to build Boolean strings in ColumnD. In ColumnD...
  2. B

    Prevent Save and Save as unless using Macro Button

    Good afternoon! I am trying to set up a Workbook so that using Save or Save As is disabled but Macro buttons that do specific Save As commands are allowed. I have the following code which works well at disabling Save and Save As, but it won't allow macros that are tied to buttons to perform a...
  3. N

    Code error

    Hi, I have these codes in Workbook Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim ws As Worksheet Set ws = Worksheets("CHIT") If Sh.Name = ws.Name Then ws.Range("C12") = Application.UserName End If Set ws = Nothing End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As...
  4. K

    How to pass a User defined type as a function parameter

    I use VBA all the time but have never tried to use Type. I haven't quite figured it out. Not sure how to pass the parameter into a function. Basically i have a function that i want to call to find a value in a range based on a condition. The condition is a type and based on the type i will find...
  5. kelly mort

    Delete file in a folder with vba or override with new file

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim backupfolder As String backupfolder = "C:\BackupFile\" ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name End Sub Hi, The above code is what I am using to create a backup of my...
  6. S

    VBA Before close

    I'm looking to give users an option when closing a workbook and have put in the following VBA code, but I cant figure out how to trigger the opening of another workbook if 'yes' is selected. Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you need to refresh timesheets?"...
  7. T

    Changing a boolean string to multiple records (or vice versa)

    I receive an excel file each week that has the race of individuals noted as a single six character Boolean string (e.g., 100000 or 011000 or 000111 etc.). Each 1 or 0 is a "yes" or "no" to the corresponding races. First digit "White", second "African American" etc. As you can see, an...
  8. M

    Error Message when running code, Runtime Error 9, Subscript Out Of Range

    Hi, Can someone tell me why im getting this issue Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If (Sheets("Supplier Checklist").Range("D24").Value) = "No" And Sheets("Supplier Checklist").Range("E21") = "" Then SaveAsUI = False MsgBox "Please define...
  9. S

    Find . . . Using Boolean, Dates, and Wildcards

    I found out how to do this in Win 10 File Explorer: Learning Windows Search: Use Boolean, Dates, and Wildcards to Give Your Searches a Boost . . . I don't see that that syntax works in Excel 2010's "Find" Do you have to use a...
  10. T

    AMBIGUOUS NAME DETECTED: Workbook_BeforeSave

    Hi guys, I'm new to the forum so apologies if I've already broken a rule/some rules by posting this. I am trying to create an excel for work that doesn't allow the user to save if a cell is blank. Having copied some VBA coding from the internet, I currently have this in the workbook module...
  11. L

    Bold is Variant not Boolean

    Hi Excel define Bold as a variant. Yet the example below (from Assigned True to Bold!! So why excel did not define Bold as a Boolean? It would make sense to be define as Boolean. Thank you. what does it mean a variant variable...
  12. D

    How to return the SUM of several multi-criteria INDEX/MATCH results?

    Simplifying as much as possible, my data is structured as below. What I'm trying to return is the sum from one of the component cost columns of table 2, of entries that are "X" category and "TRUE" boolean from table 1. In the example below, the sum for "X" category, TRUE should return $4.00...
  13. S

    Save As Cell content

    I currently have the Save blocked to where you can only do a Save As. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not SaveAsUI Then Cancel = True End If End Sub But I also am wanting when you go to Save As it will have the contents from...
  14. D

    Posting a Range

    Hi I hope you can help. I have a range of cells on one sheet - this range can change depending upon contents. So, For example, the entire range is between cells B19 and G31 I am thinking - if I copy this range to an Array then how do i tell the array that I don't want blank cells when I copy...
  15. M

    Changing cell color in a range of cells

    Hello, I have figured out the VBA formula to change cell color when double click, right click etc... however, i only want it to this on a specific section of the worksheet and not the whole worksheet. Here is what i entered in the VBA Private Sub Worksheet_BeforeDoubleClick(ByVal Target As...
  16. B

    Optimize dynamic creation of labels and their click events.

    So my code allows to create a label dynamically (CreateLabel Sub) and dynamically remove labels (RemoveControls Sub) and the class module allows for the click event to occur for these dynamically created labels. The thing is I am using both collection and arrays to make this happen but I feel as...
  17. B

    reformat name

    I have a macro that returns the user's name in this format:"Doe, John". What I want is to be formatted like:"John Doe". Here is the macro I am working with. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ws As Worksheet Set ws = ActiveSheet With...
  18. D

    ActiveChart does not work when running a batch. How to solve it?

    Hi Guys, I am having difficulty running a small report via Task Scheduler. It works fine updating form the database but this last code gives me a headache. The error says: "Run-time error '91': Object cariable or With block varible not set I know its the highligthed with red color, but just...
  19. D

    Prevent save as with VBA

    <code>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "You can't save this workbook!" Cancel = True End Sub I can't seem to prevent the user from saving as and creating a new workbook, and yet still allow them to save the workbook regularly. </code>
  20. M

    VBA code to re-protect sheets of the workbook upon close and save that will also allow auto-filters to work

    I am new to MrExcel and new to VBA and hope you will help me. I used the following code to protect my workbook sheets before close and upon save and it works great but I can't figure out how to write it to allow filters to still work on all tabs of the workbook when protected. There are...

Some videos you may like

This Week's Hot Topics

  • Date update overflow error ‘6’
    Dear MrExcel community, I have a project search & update & add & delete can do. There are 32 column and 5 of them is date. When I search date...
  • Highlight Row and Column on cell selection
    In the following I have some code I need modified to see if I can do the following: 1) Only have this work when selecting a cell inside a couple...
  • Need Numbers Inputted and Deleted
    Thanks for your help. Not sure if there is even an = equation for this. Please see attachment. If there is a solution, please respond with = So...
  • Worksheets(x).Select Error
    Hi, I am trying to select multiple sheets using the following code. [CODE=vba] Dim x As Integer ThisWorkbook.Worksheets(3).Select...
  • myFind Range not working
    Please can someone say why this myFindRng goes red in the code? [CODE=vba]Private Sub Model_Chose_Change() With ThisWorkbook.Worksheets("Job...
  • How to identify the last page break in a sheet
    How can I find the position of the last page break in my sheet? There may be multiple pages and I want to know how to find the position of the...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back