Posts by Pesky Weasel

    Re: Filling a range without activating the workbook


    Justin,


    I really hope I got this right now coause its getting embarrassing. What I neglected to do was use the full name of the named range (see below).



    In this example -
    Target workbook = test.xls
    Target worksheet = Sheet1
    Named Range = rngtest


    Notice that when you you refer to a named range in another workbook you dont need to include the name of the workbook as a 'Workbooks("test.xls")' argument, but rather in the 'Range("test!rngtest")' argument. I did not know this until now :) which is cool cause its a double whammy when you help someone and learn something new.


    Hopefully we can put this issue to bed (but please tell me if i've bolloxed it up again.... i've got to get this right)
    Regards
    Weasel

    Re: check if read only and inform my users


    Ahhhhhhhh, I too understand.


    I was under the impression that you were opening the excel file from windows (or some other OS), and not from an existing instance of excel.


    Not that I could have provided an example.... :)


    Regards
    Weasel

    Re: Filling a range without activating the workbook


    Justin,


    So sorry, my bad. I was testing code in the activeworkbook. Stupid, stupid stupid :smash:



    Here is a test scenario that works (maybe) - adapt to your needs:



    Regards
    Weasel

    Re: macro error


    Wow,


    The debugger recorded some weird code there, and no It wouldnt work.


    The two options to insert an entire column will look like this (using macro recorder):


    1 - Single cell selected:

    Code
    Selection.EntireColumn.Insert


    2 - Selecting entire column:

    Code
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight


    Regards
    Weasel

    Re: Detect space, underscore or semi-colon in text string


    Dude,


    Both Norie & Daves answers are the way to go. Yes the InStr tells you the position of the character, and while this may not be important a return of 1 or more means that the character exists.


    Hence

    Code
    x = InStr(1,myString,myDelimiter)


    If the value of x = 0 the character doesnt exist in the string, if its 1 of greater it does.


    Regards
    Weasel

    Re: User form display previous posted data


    Bari,


    If the value in the textbox is always in the same cell then it would be more efficient to set the ControlSource of the textbox to that cell.


    This can be done in the Properties window for the textbox control within the VBA editor.


    Regards
    Weasel

    Re: Filling a range without activating the workbook


    What code are you using that causes the error to appear?


    You will need to include the name of the target workbook with the name of the range to refer to a range in another workbook. e.g.


    The target workbook is names test.xls
    The target range in test.xls is names rngTest


    Code
    Range("test!rngtest").Value = "testing 1,2,3...""


    Regards
    Weasel

    Re: check if read only and inform my users


    The read only and enable macros dialogs appear before the file is actally opened. As such they will appear before any code contradicting them can be run, so in short, NO (AFAIK.... always best to cover your bases here :) ).


    Regards
    Weasel

    Re: check if read only and inform my users


    gibbo,


    As Will has said this does already happen and you really dont want to overload the users will repetative messages or they start to ignore them, but...


    this code in the ThisWorkbook module will do the trick


    Code
    Private Sub Workbook_Open()
    If ThisWorkbook.ReadOnly = True Then
    MsgBox "This book is read only, make changes if you want but you will be wasting your time."
    End If
    End Sub

    Re: Picture object


    DoD,


    Welcome to Ozgrid!


    I had no problem using the method you have described. Just to confirm, you are using Tools - Protection - Protect Sheet to protect all locked cells (locking cells & other objects has no effect unless the sheet is protected).


    Regards
    Weasel

    Re: Selecting a Cell in the (1:1) row


    Quote


    My Active cell could be anywhere in the sheet.
    But what I'd like to do is have it go to the top row and offset by 1 column


    EG: if Active cell is in say B25 then want the active cell to move to A26. Normally this is easy to do but the activecell could be anywhere.


    This bit confused me a little, could you confirm:


    In the first sentance -
    The active cell is B25
    The top row is 1
    The offset is activecell.column +1
    This would make the target C1
    this is consistant with the title of the post.


    In the second sentance you ask for B25 to go to A26 -
    This is the activecell.row +1
    activecell.column -1
    or
    offset (1,-1)



    Which is the desired result?


    Regards
    Weasel

    Re: Conditional Save Vba Count


    Another method that will simplfy your original code would be to use the Mod function:



    Regards
    Weasel

    Re: Linking Sheets


    pace,


    The INDIRECT worksheet function may be what you need.


    e.g.


    The reference sheets are named Sheet1, Sheet2, & Sheet3. In cell A1 on each sheet is a value.
    On Sheet4 in A1 enter: =INDIRECT("Sheet" & A2 & "!A1")
    In A2 you enter 1, 2 or 3 to return the values required.


    what the formula does is create a text string using the strings in the quote marks and the value in A2. ie. Sheet1!A1


    This should adapt to your needs.
    Regards
    Weasel

    Re: ComboBoxs/Dropdown menus in VBA


    Ant2ny,


    Using Nories suggestion of the worksheet_activate event see the following code:



    Regards
    Weasel


    ps: theis code may provide the answer you need but if you dont understand it please ask questions, read, research and practice until you do. We are not here to do homework, but to help others (and ourselves) learn and gain new skills.

    Re: ComboBoxs/Dropdown menus in VBA


    Ant2ny,


    Quote

    Heres how i have kinda done it.
    View > Toolbars > Control Toolbox > select combo box and draw it > right click the drop down menu and click view source.


    I would assume that the option you are choosing is View Code as View Source is not an option under that menu. If this is correct, what is displayed is the VBA module for that particular control.


    Yes, you can use VBA to dynamically populate a combobox. Sometimes this is very handy, especially when the contents of the control need to change based on information supplied by the user. Sometimes though, its just not neccesary and is much harder than your original methods (please note that the first method you described is not creating a combobox, rather a validation list - same result in many ways but may not be the same thing as far as exams go).


    Have a seach of the board, there will be plenty of examples, or if could provide some more info about where the source of data is etc. it would be easier to recommend a plan of attack.


    Regards
    Weasel

    Re: Networkdays Function


    Rob,


    Without actually setting up a test bed, I do see one immediate comcern:


    1) Not all months have 31 days. (Do Until n = 31)
    2) Days that have 30/31 days can include 5 Saturdays (eg. this month, April, July....)
    3) If you are looking at 1 Saturday in 4 maybe you should be basing your timesheets around 28 day cycles rather than calander months.


    Regards
    Weasel

    Re: Networkdays Function


    Rob,


    Do you mean that you wish to remove 'Bank Holidays' from the calculations?


    If so, this is not so easily done as these days change year by year, country by country. You will probably need to keep a list of them somewhere in a worksheet, however this has disadvantages:


    1) If you ammend the dates each year any instances of the function that refer to previous years will not be accurate.
    2) If you continually add each years holidays to the list the function will be exponentially slower as the the number of usages & the number of holidays both increase.
    3) This function will only operate in worksheets that contain the list.


    Can you provide a little more information as to the scope of the spreadsheet and we may be able to advise if such a case is worth the effort.


    Regards
    Weasel