Posts by StefanG

  • Hide Rows on Multiple Worksheet Pages

    Re: Hide rows on multiple pages


    Hi,
    PMFJI
    I found this to be working for me.

    Code
    Sub MultiplePageHideRows1()
Dim I
    For I = 1 To 3
        Sheets("Sheet" & I).Activate
        Rows("2:2").EntireRow.Hidden = True
        Range("A1").Select
    Next I
    Sheets("Sheet1").Select
End Sub


    Stefan

  • Place Button (Forms Toolbar) Based on Cell Address

    Hi,
    I am seeking to place a Button from the Forms Toolbar in a Spreadsheet via VBA. Currently i have this bit

    Code
    ActiveSheet.Buttons.Add(220, 210, 155, 26).Select

    How can I fix it so that the button is added at a specific cell? I.e. Top left of the button should be top left of cell A17, or can i find the center of say A17 and it for the first two reference points?
    Thank you,
    Stefan

  • Autosize Cell Comments/Shapes via Macro Code

    Re: autosize comment errors with recorded macro


    Hi Andy,

    Quote

    The selection is not refering to the shape textframe.

    Thank you. - Wonder why the Macro Recorder is doing such garbage? - On the other hand, i suppose that there is not a "1-line approach" to autosizing the comment-box.
    Stefan

  • Autosize Cell Comments/Shapes via Macro Code

    Hi,
    Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.


    I just want to add

    Code
    .AutoSize = True

    or something to that effect to the bit below. Any suggestions?

    Code
    Range("A14").Comment.Text "Ladida"


    Thank you,
    Stefan

  • Lock format in cell - even if copy and pasting to that cell

    Re: Lock format in cell - even if copy and pasting to that cell


    Hi Jim,

    Quote from JimFuller1

    That's odd.. I wonder what paste constants 1-7 stand for...

    I couldnt find anything documented, but found this to be true:
    1= xlPasteAll
    2= xlPasteFormulas
    3= xlPasteValues
    4= xlPasteFormats
    5= xlPasteComments
    6= xlPasteValidation
    7= xlPasteAllExceptBorders
    8= xlPasteColumnWidths
    ... and couldnt find an operator for
    xlPasteFormulasAndNumberFormats
    xlPasteValuesAndNumberFormats
    ... or an operator to combine these into one line of code. So i guess something like this has to do (?).

    Code
    With Selection
        .PasteSpecial Paste:=4
        .PasteSpecial Paste:=6
    End With


    Then again, why not stay with the "real" code instead of having to remember what the ## stand for... unless confusion is the goal ;-).
    Stefan

  • Lock format in cell - even if copy and pasting to that cell

    Re: Lock format in cell - even if copy and pasting to that cell


    Hi,

    Quote

    cjc:
    I get a runtime error "pastespecial method of range class failed."

    I'm unable to reproduce the error. However, MS-KB article 231090 addresses this error and appears to be related to "xlColumnWidths". So I wonder if the (adjusted?) code you used caused the error¿

    Maybe you could, as DOC suggested, post a sample, or see if, when you add, as Jim has in this code, On Error Resume Next, which would skip the error, it works (but will it still perform as needed?)
    Stefan

  • Lock format in cell - even if copy and pasting to that cell

    Re: Lock format in cell - even if copy and pasting to that cell


    Hi DOC,

    Quote

    will it throw an error if say 13 numbers are added


    Well, that would be sorted out with the data validation, which restricts input longer/shorter then required. I could not figure how to do that in the code, hence thought why not have a "mastercell" and copy its correct formatting and validation... btw, this does not allow to paste single cells in the set area, which seemed to cause some problems with the users in the given example. I'm sure there are nicer and much better ways. This seemed like an interesting problem and thought to give it a try... we'll lern more, when the pro's step in, as usual.
    Stefan

  • Lock format in cell - even if copy and pasting to that cell

    Re: Lock format in cell - even if copy and pasting to that cell


    Hi cjc,


    I'd be interested to see other solutions.


    This is the best i can produce. Maybe a start? Please note that this example assumes that you have A1 formated with your standards: Custom Format being #-#####-#####-# and DataValidation being Custom with the formula =Len(A1)=12, restricting the user to enter 12 digits, no more, no less. You could adjust to accept this and/or more and/or less with something like this =OR(LEN(A1) = 12, LEN(A1) = 15)


    Stefan
    p.s.
    This would need to go into the Sheet module where users have the problem applying the right input.

  • If Macro is disabled

    Re: If Macro is disabled


    Hi guggu,


    In my little world I would use

    Code
    Private Sub Workbook_Open()
    Sheets("Sheet2").Visible = True
    Sheets("Sheet3").Visible = True
' etc etc etc
End Sub


    in 'ThisWorkbook'.


    Something to to consider is to hide the sheets when closing the workbook, because what if the workbook was saved with sheets visible... then you get an error. I guess you could check, if xyz is visible dont do a thing, or, what i'd add is a 'On Error Resume Next' before the ...visible true...?


    I suppose something like this

    Code
    Sheets(Array("Sheet2", "Sheet3")).Visible = False 'add/adjust as needed

    could be placed into a 'Private Sub Workbook_Close()'.


    I'm sure there are more genius ways to do that though.


    Stefan


    edit:
    While this may be "a given", the above assumes that the workbook was saved with the sheets hidden to begin with.

  • Saving Just One Sheet?

    Re: Saving Just One Sheet?


    Hi,
    I'm not sure why it would tell you the file already exists...
    Try this and see if it still errors.


    It basically adds the current time (hh-mm-ss) to the file name, which makes it nearly impossible to duplicate a file name. I wonder if you'll still get the error?¿
    Stefan

  • Saving Just One Sheet?

    Re: Saving Just One Sheet?


    Hi sweather,

    Quote

    Run Time Error 75

    I believe that is because the directory/folder "CharterWorkbook" already exists.
    Among other options (check the forum here for these), i suppose, you could change

    Code
    MkDir ("C:\CharterWorkbook")

    to

    Code
    On Error Resume Next
MkDir ("C:\CharterWorkbook")
On Error GoTo 0


    Stefan

  • Conditional Cell locking

    Re: Conditional Cell locking


    Hi Steve at work,
    Have you tried the MacroRecorder |Tools |Macro |Record New Macro?
    I suppose you could start by recording the three respective steps to
    - unprotect your worksheet
    (- lock all cells/columns)
    - unlock the cells/columns in question for each of the three instances
    - protect the worksheet
    Stefan

    Edit:
    p.s.
    Another thing to think about would be. Do you want check boxes, so that the user can check 1, 2 or all 3 and hence unlock all one, two or all three ranges at once - well i suppose you can have the respective macro uncheck the respective other box(es)...? - Maybe a radio button, where you could only have one of the three selected ...?

  • autoreplace "label" name in UserForm

    Re: autoreplace "label" name in UserForm


    Hello,


    Okay i suppose that someones "right" way is another ones "wrong" way.


    The labels have data throught a workbook to present a report. This started with a couple here and a couple there, and now there are 100 labels.


    The labels have sort of a unique references and my thought was that a find/replace could help in renaming them. Say, find "Label1" (which is in the strings from "Label101" through "Label131") and replace it with "Count1". This would result in the change to Count101 through Count131. The same could be dones with the Labels2xx, Labels3x and the Lables4x - so i thought.
    Stefan
    p.s.
    Since i did this already manually, i think i better consider this resolved.

  • ActiveCell.Offset - copy range

    Re: ActiveCell.Offset - copy range


    Hi Wigi,
    Thank you for your reply/question.
    I wanted to copy a range above the active cell. The shape/range is between 1 row up 2 cells left to 1 row up 1 cell right - for a total of 4 cells, i.e. active cell is C2, the shape/range were to be A1:D1.
    Stefan