Posts by yjoshi

    Hi Doug,

    Your suggestion works, as expected, but i was looking for a different way

    I read a book yesterday on VBA and realised that what i was expecting is the "accelerator" for the command button.
    (which is activated with Alt )

    Did a lot of R&D and came to a conclusion that I cannot assign an accelerator, as when i create a command button on worksheet, it is treated merely as a Shape! and does not support the .accelerator property.

    Any expert to comment on that????

    Hi Suresh,

    I think, I should have thought of it.:(

    Thanx for jumping in this thread and giving the solution.

    I just want to add the risk factor in the method you have suggested!

    This will delete all the rows containing all kinds of errors, which may not be desired, so it is better to check which type of error is it!!!

    When a range/sheet is deleted to which other formula is referring to, we get error "#REF" which has error.type = 4

    In the example you have taken, it will be safe to add following formula in column E
    and then if the result is true (i.e. all the errors are due to sheet deletion) it is safer to delete the rows.


    This can also be done thru the VBA if the data to be handled is huge.



    How do i create a shortcut key for buttons placed on a worksheet?

    Implemeiting on previous advice from Dave


    The approach I normally take is to have all but one sheet XlVeryHidden and leave them that way always. The sheet that is visible is simply a blank sheet without gridlines scroll bars etc.

    in thread

    I have successfully started the work.

    Now the problem is, i have placed some buttons on the worksheet, say "First", "Last" which bring the respective records.

    Help Required for:
    How to create a shortcut key for these buttons?
    (In VB i remember having some option while entring captions, to do this)

    Any such facility when a button is placed on worksheet???

    Following are the two functions i created

    Public Function get_num(num As String) As Integer
    Dim x As Integer

    For x = 1 To Len(num)
    If IsNumeric(Mid(num, 1, x)) Then get_num = Mid(num, 1, x)
    End Function
    Public Function get_text(num As String) As String
    Dim x As Integer

    For x = 1 To Len(num)
    If Not (IsNumeric(Mid(num, 1, x))) Then
    get_text = Mid(num, x, Len(num))
    x = Len(num)
    End If
    End Function

    Then in the worksheet, separate the number and text in different cells and sort with two parameters, first by number then by text.

    Hope this works for you....

    Let me know if anything else is required!!!
    Also attached one sheet to demonstrate this.

    I think the problem is more complicated than first appeared.
    We need to take-up two tasks!!
    1.Separate Numeric and Alphabetic data in each cell
    2.Sort first on number then on alphabet in Data->Sort

    While i am trying to create a function which will give these values, i think this addition will be a headstart to others.
    (or others can stop me, if i am taking a wrong path)

    Certainly, Many would like to help you, but if you help us help you...



    What i can guess from this is.

    1. When you open a workbook there will be 7 worksheets.
    2. At times some of the worksheets will be deleted, only if they are empty (absolutely blank ??)
    3. Delete the rows from the existing sheet, where the data comes from the deleted sheet

    Now the question is, if the sheet is blank, how the data will come from those sheets?

    Are there any pre-defined rows, which fetch data, even if there is no data?

    I am sorry for getting into too many details, but it will make the picture clear, and you will really get the answer that you disire and even better suggestions :cheers:

    P.S.: I am not sure about your data, but it can be a good idea to use Named Range with Offset formula, so that, the data will be picked, only if it exists.

    Hi Chris,

    Thats what i like about this forum.

    I made a small mistake, and actually many inculding you got an answer to your long outstanding problems!!!

    Is it a good idea to edit the message text, say "importing data from .mdb files now?

    (ofcourse keeping my name in subject will surely make me popular, but, changing the text can help others)


    Hi Dennis,

    Just a thought, and want some guidence from you.

    does the
    Sub Worksheet_SelectionChange

    affect performance?

    As this will be fired each time we change selection?

    Your guidence will be appreciated,
    :bouncy: as i am always in dilema as to whether to use the existing events or use macro (Button or shortcuts)

    I think method i mentioned will be a headstart for you.

    If that is the exact outpout you desire, then post accordingly, and there should not be any problem in creation of a script to do this jub.

    Hi Dan,

    Welcome to the forum...

    Someone here will certainly help you, if you can give detailed information.

    Confusions you can clarify:
    1. What is the exact stuff you want to achieve?
    2. You are taking average of a single cell (H45), what is the relevance?

    if in doubt, please attach the sheet (or a sample) here, and someobe like me will jump on it :)


    Hi onesupercooper,

    welcome to the board.

    Just to add to noeyedeer's reply.

    The formula will give you the result, but there will not be a +ve sign for +ve difference.

    For this, select the column in which you are calculating the difference.
    ->Format->Cells (or Ctrl +1)
    ->Number tab
    ->Select Custom
    ->Paste following in Type field

    This means that the +ve will numbers will be prefixed with "+" and negative cells with "-"

    (Also keep post one query for your problem, i was almost going to post it in your other problem and just discovered noeyedeer's comment :coolwink:)

    I think i have hit the bull's eye::thumbup:

    Public Sub comment()
    Dim sel As Range
    Dim comment

    For Each sel In Selection
    If sel.Value <> "" Then
    comment = sel.Offset(0, 1).Value
    Range(sel.Address).comment.Visible = False
    Range(sel.Address).comment.Text Text:=comment

    End If


    End Sub

    I have also attached a sheet.. alongwith a button you want to click to do this for you.

    see if this is ok


    Its not very clear, what exactly you want...

    But i take an example that there is one column rep and some other columns
    rep has multiple repetitive records.

    You want record for each to be given on different sheets.

    This can be done by VBA code,
    But really to go easy way...
    Create a pivot table out of it with "rep" as row and "count of rep" as data
    then double-click on the count numbers in front of each rep, and you will get the data on different sheets,

    hope this makes sense.

    I have uploaded one sheet as well.
    In Sheet1 there is data
    in Pivot there is a pivot table
    Just double-click on the numbers (11,10,etc) and you will get the data for that rep in different sheet.

    If this is exactly you intend and this path is too lenthy, please update accordingly.
    Me or someone here can surely automate this without using pivot tables.

    How about this?

    Somehow i prefer to use built-in functions instead of macros:

    -Select the range of 100 numbers and give it a name "Match_To"
    -Paste following formula in B2
    -Just copy the formula till the end (B25001)

    and thats it, nothing else

    try it!!!