Posts by Mawhrin

    Re: Any way to deselect a select in VBA?


    Hi there,


    There are 2 simple ways to achieve this, first, don't select the sheet to start with

    Code
    ThisWorkbook.Sheets("Sheet1").UsedRange.Value = ThisWorkbook.Sheets("Sheet1").UsedRange.Value


    or, just select another range when done

    Code
    ThisWorkbook.Sheets("Sheet1").Range("A1").select

    Re: Adding current date and time to multiple columns


    Hi there,


    A small change as follows:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("D2:D100")) Is Nothing Or Not Intersect(Target, Range("G2:G100")) Is Nothing Then
            Target(1, 2) = Date
            Target(1, 2).EntireColumn.AutoFit
            Target(1, 3) = Time
            Target(1, 3).EntireColumn.AutoFit
        End If
    End Sub

    Re: Find Cell Value in Range


    Hi excedrin,


    The code is fine, it just depends on the behaviour you want & where. If you want the copyright symbol automatically added after entering a value then you will want the sheet events for change as below. It will be required for all sheets you want this to be done for.

    Re: Macro to open Word Document through Excel


    Hi there,


    This will dowhat you want assuming a full path & document name in B1. Make sure to reference the word object library to make it work.

    Re: Find Cell Value in Range


    If you want to upload a workbook to look at it would be helpful. But it sounds like this can be done without VBA using a mix of match & char worksheet functions.

    Re: Find Cell Value in Range


    Hi,


    You should not have added ByVal Sh as Object to the declaration line.


    If you just want the copyright symbol you canuse the worksheet function =CHAR(169).

    Re: Altering email code to include other attachments (pdf of workbook)


    Hi there,


    You'll need something along these lines to create the pdf file


    And you should be able to add the pdf with

    Code
    .Attachments.Add TempFilePath & TempFileName & ".pdf"

    Re: Kind of editable DataGrid in UserForm


    Hmm, initial reading suggested it might fit the bill but further investigation suggests it's no longer supported which is a shame.


    That being so if a useform solution is required I would just layout textboxes & comboboxes & place them inside a frame if you need to move them en-masse.

    Re: Data Validation? Index? Transpose? Offset? Not sure the best path.


    Hi there, it's not clear exactly which responses are driving what tasks as a result so it is difficult to be overly helpful. But looking at the variety of tasks this might be best accomplished using a command button click event, it really depends on how the responses are related to the derived task list.

    Is it possible to find out who has a workbook open when the workbook is not shared? i.e. User1 opens the workbook, User2 then tries to open the same workbook & gets the "do you want to be notified" message indicating someone else is using the workbook. I would then like to be able to run a macro to return the username of whoever has the workbook open so I could send an email but cannot find a way of accessing the "notification list".

    Re: If folder doesn't exists


    Quote from zplugger;738341


    Dim strFolder AsString'Folder to check the existence of with a \ at the end
    strFolder = "c:\Sales


    The problem is also fixed if you include the "\" as per the comment which you have not done in your code. This can be picked up & corrected with

    Code
    If Not Right(strFolder, 1) = "\" Then strFolder = strFolder & "\"


    after you have assigned a value to strFolder

    Re: Excel to Excel Data Connection Trouble


    Hi Cytop, many thanks for taking the time to respond, it is appreciated.

    In answer to your questions, the backend database can really be either an Access DB or an Excel WB with data tables; although I know next to nothing about Access I do think it would be the preferable option seeing as I understand it will handle the unique IDs without me then needing to handle this in VBA for an Excel solution.

    As for the connection method, again, I am flexible about this but think it would be best to be able to run queries via SQL & avoid having to open the source database as over time this will be large & potentially slow to load. I will need the ability to update records as well as just read them so it will need to be a read/write connection. I had/have a working solution to just read the required results from a CSV file, however, this does not allow me to update the entries or I am failing to find the correct syntax/method to do so.

    If it should help in anyway here is the code used to access the CSV file, this is how it starts with a button click:



    The next part puts together the SQL query:



    This opens the connection:



    And this is to display the results:

    Re: If folder doesn't exists


    try this

    Re: If folder doesn't exists


    Quote from zplugger;738213

    Here is the code I use now,works perfect if folder exists. Range C13 is the folder name


    This doesn't show me where & how you've tried to incorporate the new code.