Posts by gollem

    Re: Autoreplace does not work when importing .txt


    I could be wrong but the code will never work because:

    Select Case UCase(Trim(cell.Value))

    Case "greyt"

    You do an upper case so the value would be "GREYT" => capitals.
    The code should work better when all your cases are capitals.

    Select Case UCase(Trim(cell.Value)) 
            Case "GREYT" 
                cellVal = "Great" 
            Case "LOCK"

    Re: update files using a vba script

    Of course this is possible, if you program it : D

    You can use variables for this, I don't if it are numbers or text, I assume values.

    Hope this gives you an idea

    Re: Date using combo box - VBA


    I'm not sure but I think if you create the date time picker control in 2000 all versions after should be able to read it. Perhaps Excel 97 has already one, I don't know.

    Re: Adding Comment with Protected Sheet


    Shawn I've tested your problem and indeed it's not working.

    I've got 2 solutions, actually 3 but you've already mentioned it:

    1) When you protected the sheet don't check the objects checkbox and the code will work
    2) Another posibilty is to add an empty comment box before protecting the sheet and then only use the text-property of the commentbox:

    Range("F11").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & ""

    Hope this gives you new ideas.

    Re: VBA code for transfer of data

    Hi Russell,

    I'm sorry you think that way of the forums, but you have to know that most people here don't do this for a profession, just to have fun or to learn something. People here help you in there free time and don't get paid or anything.

    Another point is, it's not the idea that people do the work for you, you have to do some work yourself. We can give some solutions and tips but the eventually result should be made by yourself.

    The last remark I give is that it's not simple to give a perfect answer to a specific problem when we only have a description, there could be 100 ways to solve a problem but without examples it's sometimes harder. You see your patients and you can talk and examine them. Don't expect that 1 post will always give an answer, be patiented and people will help you.

    I've made a little example workbook, based on the info I've got from you. I hope this gives you a start.

    Re: access of workbook


    what do you mean, do you want to see everybody who has accessed the file for ex the last 2 days? I'm not sure this is possible by a simple command.

    You can always write a macro that writes a log everytime the file is opened by a user.

    Re: Password box for spreadsheet open


    just a little remark:

    use xlSheetVeryHidden instead of xlSheetHidden otherwise the user will be able to see the sheets. Don't forget to set a password on the Vba code, otherwise it means to nothing.

    If you want a readonly copy opened, I think you should program this code in another program or workbook that opens the specific workbook in readonly-mode. You can always do this on the windows-level, by setting the security of the file.

    Re: Macro works only once/LogIn

    This you can check if you place a messagebox in the login procedure.

    msgbox Application.UserName

    You can place several msgboxes to check where the problem occurs. Just a tip of course, according to me your code is ok.

    Re: Macro works only once/LogIn


    I'm not sure what the problem is but the login executes on opening the workbook. So executing it twice isn't done here... When doesn't it execute twice? When should the macro execute twice?

    Re: Run-time error 380


    you've said that the error occurs when you have an empty row, well you have to check the cell then:

    'If we have a value then set the value in the box, else do nothing
    if ActiveCell.Offset(0, 2) <> "" then cboChannel.Value = ActiveCell.Offset(0, 2)

    This should do the trick, you can do this for all your items.

    Re: Run-time error 380

    As iwrk4dedpr already told, if the item isn't in the box: a blank for example, you get the error.
    You can solve this by checking the value. Check if it's a blank, if it's not a blank => take the value in the combobox, if it's blank => skip

    Re: Cancel Application.OnTime


    sorry already found it.

    Just store the time when the macro is sheduled and then call application.ontime but set the shedule to false.


    You need to pass the dynamic time to a static variable to Cancel an OnTime method like you have. See ONTIME


    I use the application.OnTime command to run a specific sub "test".

    Application.OnTime Now + TimeValue("00:00:50"), "test", schedule:=true

    Now I've got a global question, just wondering, not urgent but interesting to know. Can I cancel the defined macro that will run on a specific time. Let's say the macro is sheduled and 5 seconds after I want to stop the macro without waiting until the macro is runned. Is this possible, some thoughts?


    Re: Auto running a macro based on date

    Ok, I've uploaded your file with the code. I've added a worksheet "check" to check if the macro already runned. (You don't want the macro to run every time you open it)

    This is the basic code that's executed when you open your worksheet:

    Hope this helps.

    Succes ermee.:)

    Re: Auto running a macro based on date

    It can be done, but it will make it much harder.
    Maybe we can start with interaction of the user. Later we can perhaps built in the automatic run of the whole app.

    I'll take a look at it.