Posts by The Dutchman

    Hi Dave:


    Thanks for your reply. I have inserted the suggested code into my double click event with no change in result... I still get the hour glass.


    The total modified code is as follows:


    [code]
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    Me.Protect Password:="`", UserInterfaceOnly:=True


    If Not Application.Intersect(Target, Range("F11:G60")) Is Nothing Then
    Cancel = True

    If Target.Count > 2 Then
    Exit Sub
    End If



    Dim c1 As String
    c1 = ActiveCell.Address
    Range("cell_Address1") = c1
    c1 = WorksheetFunction.Substitute(c1, "$", "")
    UserForm1.CommandButton2.Caption = "Post GL Description to Cell " & c1
    UserForm1.ComboBox1.SetFocus
    With UserForm1
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
    End With
    End If

    If Not Application.Intersect(Target, Range("K11:K60")) Is Nothing Then
    Cancel = True

    If Target.Count > 1 Then
    Exit Sub
    End If

    Dim c2 As String
    c2 = ActiveCell.Address
    Range("cell_Address2") = c2
    c2 = WorksheetFunction.Substitute(c2, "$", "")
    UserForm2.CommandButton2.Caption = "Post GL Description to Cell " & c2
    UserForm2.ComboBox1.SetFocus
    With UserForm2
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
    End With
    End If


    End Sub


    Any other suggestions?


    Thx

    I have a an expense workbook that has a column (2 columns merged) where the user can use a validation list to select an expense description OR by double clicking on that same cell a userform with a combo box appears and the user can type in the expense description. When the worksheet is unprotected it works fine but when the worksheet is protected (even though the cells in question are not protected) all I get is an hour glass and then I have to press the "esc" key to exit.


    I have used the following code when opening the workbook to assist"




    In the code for the double click event I have also entered the following code before and after the code in question executes:




    Any ideas as to how this can be rectified?


    Many thanks,


    Ray

    I am making a scheduling system for one of the departments in my business. To make it easier on the user, I have set up a number of common "Start & Finish" times as named ranges (each named range is two cells wide); in the example below one of these named ranges is "sevenam". At the same time I have created a named range (called Paste_Range) for the various cells/columns that a "Start" time can be inserted so the user does not paste Start & Finish times into the wrong columns. (The Paste_Range represents 28 smaller ranges within the same tab)


    To execute the copy & paste (as values) function for the user I am setting up a number of macros that correspond with the common "Start & Finish" times; in the example below it is Ctrl+q


    The problem is that no matter where is try to execute this code I always get the Msgbox.


    Your assistance is appreciated.


    Ray


    The simple code below I write on a regular basis but for some reason the "YES" option is now ignored. If I switch the order of the "YES/NO" code, when I press on the "YES" button nothing happens (like before) but when I press the "NO" button the "Yes" script is performed. I have compared the code below to other Excel templates where I use the same code and cannot see any errors. I have also rebooted my computer to see if the problem might be hardware or system related... no luck.


    Any ideas out there?


    Thx - Ray


    I have inserted the code below (found on the internet) to unlock worksheets in an Excel file. As soon as I run the code, from the Windows Task Manager, it states that Excel is "Not responding". I have tried copying one sheet into a separate file and running the same code but Excel still stalls. Is there something special with Excel 2013 that makes this code ineffective or do I just have to be patient?


    Thx

    Re: VBA FIND function not working as planned


    Thanks for your assistance. I inserted your code and ran it without an errors (Yeah!!!) but no data was copied and pasted to the row corresponding to Dec-31-16. To make sure that the code was running properly I inserted a MsgBox at the end of the code to let me know.


    I reviewed your code and everything seems AOK but it does not paste any data. Any ideas?


    Thx - Ray

    Re: VBA FIND function not working as planned


    Quote from skywriter;770076

    Dates can be tricky with range.find, if you have a spreadsheet that you can upload I could probably help you.


    You can substitute dummy data for any sensitive data, but leave the dates alone.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

    forum.ozgrid.com/index.php?attachment/68982/



    I think I got the attachment function to work. Let me know if it didn't. I didn;t see the "Go Advanced", just the usual paper clip icon. Thanks for your help.

    In a nutshell, I have a template where daily financial data in entered and balanced into on a worksheet (sheet1). The date for this data is a named range called "Date_Today". This data is then linked into row 2 of another worksheet (sheet6) that acts as a database of all daily transactions.


    On sheet 1 I have a button that when pressed copies the data on row 2 on sheet 6 and then finds a date (already listed in a column) in a range called "Paste_Date_Data". The procedure does not like the code attached tot he "Find" function I have used.M


    My code is as follows:


    Re: Trying to pass sheet name to variable - results pass sheet number instead


    Thanks for your response Robert.


    With the spreadsheet there are other worksheets within the file. Worksheets named "1" through "31" hold the financial data that I want to copy and paste to the "Camp Track Sheet". Reading your code, it looks like it loops though all the worksheets with the exception of the "Camp Track Sheet". As well, I want the financial data to copy and paste in chronological order.

    I wrote some script that runs through worksheets named 1 through 31 and copies a set range from each worksheet to the next available cell on a worksheet named "Camp Track Sheet". Instead of the code recognizing the sheet name it pulls data from the sheet numbers so I am getting some wrong data pulled into the "Camp Track Sheet: worksheet.


    The code is as follows:




    What am I missing?


    Many Thanks!


    Ray

    Like Christmas, this problem only comes by once a year.


    We have an Excel file that creates a journal entry in a csv file format that then gets imported into our accounting software. When creating journal entries for months in prior years, when we create the csv file and then import it into the accounting software, the value of the current year has replaced the value of the prior year. When we go into the actual csv file and manually correct the value of the year, save and close the file and then reopen it, the value of the year has again reverted to the value of the current year


    I have played with Excel's autocorrect settings (as I have found on various websites) with no success.


    Has anyone come across this problem and, more importantly, found an affective solution?


    Thx - Ray

    I have a cohort who has an Excel file with a multitude of tabs. With this in mind, she also want to keep a "Summary Tab" of all these tabs within easy access at all times. I was thinking of writing some code that when a sheet is activated to position the "Summary Tab" to the left of the active sheet.


    I know that I would use Worksheet_activate() and then to write some code after that to move the tab in question to the left but what would that exact code be? As well, can code be written at a workbook level so that I do not have to replicate this code for every current and future worksheet?


    Thx

    Re: Command Button Property Name Changes Automatically


    Hi Roy:



    Here is the link. I have never shared a link like this before so let me know if it doesn't work.
    https://drive.google.com/open?…Thvakl5VkNaOTQ&authuser=0



    As well, I was thinking last night what may be the cause (yet I can't find any documentation on it) is that I made a copy of tab "Week 1" and called it "Week 2" including the command buttons. Could this be the root to my problems? At the end of the day, if you look any of the command button references in the Name Box (there is at least one command button on each sheet except for one) if is different than the command button name in the associated properties window. For some reason the command button name in the properties window will change.


    Thx - Ray

    I have a template where the command button names (two activeX controls) on one tab keep changing. Because of this, it makes the buttons inoperable because the command button name is no longer associated with the sub routine written for the specifically name control. I change the code to reflect the change in control name but then the control name changes again. I have command buttons within the same file (on different tabs) that work as planned. I have checked the code to insure there si nothing that refers to changing the


    Has anyone seen this and more importantly how can this be fixed.


    Thx - Ray