Posts by pradeep_atm

  • Comments not working during cell protection

    Re: Comments not working during cell protection


    Hi,


    Welcome to the forum!


    I don't think there will be problem in viewing a comment on a protected sheet. However, a protected sheet will not allow Auto Filter option(whereas you are saying other way around!).


    Try this to enable the Auto Filter option on a protected sheet, where I have given the OK as the Password, which you can change. Place this code in Workbook open event.


    [vba]
    Private Sub Workbook_Open()
    Sheet1.Protect Password:="ok", UserInterFaceOnly:=True
    Sheet1.EnableAutoFilter = True
    End Sub
    [/vba]


    HTH

  • Spreadsheet links

    Re: Spreadsheet links


    Hi,


    Try doing something like this:


    If('Patient Log-May 05'!D10<>"x","",'Patient Log-May 05'!D10)


    If you were to use Count function, use COUNT and not COUNTA, so that these cab be ignored.


    HTH.

  • Spreadsheet links

    Re: Spreadsheet links


    Hi CSinger,


    Would be better if you make your point more clear. Am not able to make out, when you mean carryover.


    Posting with some example will always help.


    Thanx

  • to delete all existing worksheets other than some

    Re: to delete all existing worksheets other than some


    Hi


    1. For xxxx"@"xxxxx.com use this formula in Validation.
    =NOT(ISERROR(FIND("@",C7)))
    2. For character of certain length use Text length option.
    3. For number of certain lenth use Len()< number of digits(Ex: Len(A1)<=10)


    All these use in :DV-Custom-Formula.



    HTH.

  • searching for text followed by number

    Re: searching for text followed by number


    Hi Aadarsh,


    Search UDF section of this site, you can find solution to this.


    This is it.


    [vba]
    Function ExtractNumber(rCell As Range)
    Dim iCount As Integer, i As Integer
    Dim sText As String
    Dim lNum As String
    sText = rCell


    For iCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, iCount, 1)) Then
    i = i + 1
    lNum = Mid(sText, iCount, 1) & lNum
    End If


    If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount



    ExtractNumber = CLng(lNum)
    End Function
    [/vba]



    HTH.

  • Linking multiwork books to one workbook

    Re: Linking multiwork books to one workbook


    Hi Madi,


    Well certain things to be made clear. How the records are identified(how to say a record is red/blue). And what do you mean by a record, is the reference to a row or a separate sheet.


    I don't think this is possible without programming.


    Also state what you are trying to achieve by doing this, which helps one to look at alternative ways..


    HTH

  • Sorting

    Re: Sorting


    Hi Zabanero,


    It would be difficult offer any suggestions withput actually understanding the requirement. Please post back with example/Sample data.


    Have you tried AutoFilter option(Data - Filter - AutoFilter).


    HTH.

  • full page userforms

    Re: full page userforms


    Hi Smooth,


    Try this. It will open the form to the full screen.


    [vba]
    Private Sub UserForm_Initialize()
    Me.StartUpPosition = 2 '(center on screen)
    Me.Height = Application.Height
    Me.Width = Application.Width
    End Sub
    [/vba]


    HTH.

  • color with which item highlighted changes

    Re: color with which item highlighted changes


    Well, I was referring to one possibility. If you are using somebodies file are copied the formating from somwhere, there is a possibility that the cells have that formating.


    To check the formating select the cell and press Ctrl + 1
    and for Conditional formating Format - &gt Conditional Formating and check if there are formating conditions attached to it.


    The best thing would be to attach the file so that somebody can offer some suggestion.


    HTH.

  • Vlookup

    Re: Vlookup


    Hi Geetha,


    Try this:


    [vba]
    =VLOOKUP(G2,INDIRECT("'("&+(ROW()-1)&")'!B2:C10"),2,FALSE)
    [/vba]


    In your formula "&" is missing before +ROW(). Change the sheet references accordingly.


    HTH

  • Loop if cell is red, insert into other sheet help

    Re: Loop if cell is red, insert into other sheet help


    Hi Hueby,


    Try this. This will copy values in Col A,B to Col A, Col B of Summary sheet if the cell is colored red in Col D. It is also to simple to copy the entire row to a different sheet from this.


    [vba]
    cell.EntireRow.Copy Destination:=Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
    [/vba]



    [vba]
    Sub test()
    Dim ws As Worksheet
    Dim cell As Range

    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
    For Each cell In Range(ws.Range("D1"), ws.Range("D65536").End(xlUp))
    If cell.Interior.ColorIndex = 3 Then
    cell.Offset(0, -3).Copy Destination:=Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
    cell.Offset(0, -2).Copy Destination:=Sheets("Summary").Range("B65536").End(xlUp).Offset(1, 0)
    cell.Copy Destination:=Sheets("Summary").Range("D65536").End(xlUp).Offset(1, 0)
    End If
    Next
    End If
    Next
    End Sub
    [/vba]

  • Vlookup

    Re: Vlookup


    Hi Geetha,


    Please post the formula which you have used. Is the formula returning #Ref for all the cells or some of the range.


    HTH

  • Password/User Name Attempt Count

    Re: Password/User Name Attempts Counter


    Hi Numan,


    To avoid closing of the Userform by close button have this lines code as well.


    [vba]
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
    End Sub
    [/vba]


    HTH

  • Loop if cell is red, insert into other sheet help

    Re: Loop if cell is red, insert into other sheet help


    Hi Hueby,


    Try this: Change the references accordingly. this code places the values in a sheet called Summary. Please change to suit to your requirements.


    [vba]
    Sub test()
    Dim ws As Worksheet
    Dim cell As Range


    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
    For Each cell In Range(ws.Range("D1"), ws.Range("D65536").End(xlUp))
    If cell.Interior.ColorIndex = 3 Then
    cell.Copy Destination:=Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
    End If
    Next
    End If
    Next
    End Sub
    [/vba]


    HTH