Posts by yjoshi

  • SOLVED: Short-cut keys for buttons placed on Worksheet

    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????
    :guitar:

  • auto rows delete

    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.


    =AND(ERROR.TYPE(B11)=4,ERROR.TYPE(C11)=4,ERROR.TYPE(D11)=4)


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


    :cheers:

  • SOLVED: Short-cut keys for buttons placed on Worksheet

    Hi,


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


    Details:-
    Implemeiting on previous advice from Dave


    Quote


    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
    http://www.ozgrid.com/forum/viewthread.php?tid=843


    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???
    :cheers:

  • Sorting Data: Text and Numbers together???

    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)
    Next
    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
    Next
    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.

  • Sorting Data: Text and Numbers together???

    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)
    :cheers:

  • auto rows delete

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



    Quote


    I HAVE A WORKBOOK WITH 7 WORKSHEETS, SOMETIME I NEED TO DELETE 1,2,3 OR 4 WORKSHEETS IF NO DATA THERE. THEREFORE I WANT TO AUTOMATICLY DELETE ROWS IN OTHER 2 SHEETS WERE DATA COMES FROM DELETED WORKSHEETS.


    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.

  • Yogendra.... importing data from mdb files

    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)


    :cheers:

  • Protect fields

    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)
    :cheers:

  • moving data to another file

    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.

  • Making COUNTBLANK work

    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 :)


    :cheers:

  • Formula Rookie

    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
    +###0;-###0


    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:)

  • Comments

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


    =============================
    Public Sub comment()
    Dim sel As Range
    Dim comment
    Range("a2:a20").Select


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


    End If


    Next


    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

  • moving data to another file

    Hi,


    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.

  • Matching column cells...

    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
    =IF(ISERROR(MATCH(A2,Match_To,FALSE)),0,1)
    -Just copy the formula till the end (B25001)


    and thats it, nothing else


    try it!!!