Posts by X10A

Re: Sum, Ignore Hidden Rows

Quote from ronzander1

Is there a way to keep the Sum Funtion from adding in the values from Rows you have hidden? I want my total to be the result of only the visible lines, but have reasons for hiding rather than deleting rows (I may have to unhide some later depending on other factors)

Hi ronzander1,

Dave is right. Going by your question above, you're asking how to deal with hidden rows. In that case, the Subtotal function should suffice.

Regards,
X10A

Re: Sum, Ignore Hidden Rows

Hi ronzander1,

This is what I came up with. Select the cell where you want the sum formula to be and run the below code.

Sub SumVisibleCells()
Dim SumRng As Range, VisibleRng As Range


Set SumRng = Application.InputBox("Select the range to sum", Type:=8)


For Each c In SumRng
    If c.EntireRow.Hidden Or c.EntireColumn.Hidden Then
    Else
        If VisibleRng Is Nothing Then
            Set VisibleRng = c
        Else
            Set VisibleRng = Union(c, VisibleRng)
        End If
    End If
Next c


ActiveCell.Formula = "=sum(" & VisibleRng.Address & ")"


End Sub

Regards,
X10A

Re: Stop Sum Function Adding Hidden Rows?

Hi ronzander1,

You can use the Subtotal function. I'm using Excel 2003. The Subtotal formula gives you a choice whether to include hidden rows or otherwise.

Regards,
X10A

Re: Build Hyperlink From Cells

Hi zotee98,

I think that function is under Insert -> Hyperlink. Then look for "Place in this document". is that what you are asking?

Regards.

Re: Add Border Around Selection

I just pop into this thread and wow, I never knew that it can be so easy to apply border on cells! It's great, Derk!

Re: Check Cell For Valid Time

Hi,

My appreciation goes out to all, especially Dave and Jindon. Thanks, guys!

Hi Mike Aviv,
I tested your code which is not appropriate because it will turn True as long as the cell contains “:”. e.g. “Jay : 3 cartons” will actually return True.

Hi Mavyak,
You mentioned the use of RegExp. Frankly speaking, if Jindon had not posted the code which utilise this expression, I wouldn’t have a clue how to even start. Even so, thanks for the lead.

Regards.

Hi,

I am using VBA to determine the data type of the active cell value. I have the following:

'To check Number
IsNumeric(ActiveCell.Value)


'To check Date
IsDate((ActiveCell.Value)


'To check Formula
ActiveCell.HasFormula


'To check Text (*not perfect solution)
WorksheetFunction.IsText(ActiveCell.Value)

However, I cannot find the way to check for Time.
In addition, the method IsText will return True even if the active cell value is a date.
Could someone help me with this?

Regards.

Re: Secure Add-ins

Hi,

Thanks for your reply. I know that Excel has its own VBA password protection. However, it can be hack too easily. Since there is a way to secure the code, I thought it might be a good thing to learn about it.

Right now, it's just curiosity how to use VB6 to secure an Excel project. But who knows in the future, I may need this knowledge in real life applications.

Regards.

Hi all,

I have read a lot of comments that the best way to protect our Excel add-in VBA code is to use Visual Basic 6.

I wish to know what is the process to convert the xla file with VB6. Initially, I thought it was just an easy process of opening the xla file in VB6 and a few mouse clicks. Apparently, it is more than that.

I have made a search on goggle (see webpage below) but could not understand the contents as it is too technical for me.

http://www.cpearson.com/excel/AutomationAddins.htm

In addition, it seems like the way to call a procedure (after VB6 has "hide" the VBA code) is entirely different. I am quite confused. Could someone give me some pointers?

Regards.

Re: Select All Unlocked Cells On Sheet

Hi

Thank you all for the suggestions. I'll use my code for the time being.

Regards.

Re: Select All Unlocked Cells On Sheet

Hi,
I need to distribute a workbook to different users. While testing my workbook, I often need to know whether cells that are supposed to be unlocked are indeed unlocked. Instead of going manually into each cell to check, my code will give me an overview of all the cells that are unlocked.

As I do not know how many cells will be used in the future, I used the
[vba]
For Each c In cells
[/vba]
instead of
[vba]
For Each c In Selection
[/vba]

Although it gave me what I want, but the code is taking a long time. Just wondering if there is a quicker way?

Thank you

Hi, I am trying to select all unlock cells in the active sheet. However, my method checks through each cell in the sheet and it takes too long. Is there a faster way to do it? Thank you.

[vba]
Sub SelectUnlockedCells()
Dim rng As Range

If IsNull(Cells.Locked) Then
'if null then there is unlocked cells
For Each c In cells
If c.Locked = False Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
Else
End If

If rng Is Nothing Then Exit Sub

rng.Select

End Sub
[/vba]

Re: Color And Bold Single Text Strings Within Cell Text Strings

Wow, this is good. Your comments help me understand what each line of code is doing. This will certainly be a hit, just like you said it!

Hi,

I have come up with the following code that attempts to imitate the auto-numbering function in MS Word for Excel. What the code does is to check for "Alt+Enter" entries used within a cell and will add a sequential number in front of it. I have attached a workbook that shows how it works. In addition, I have posted the code here too.

[vba]
Sub Numbering()
Dim i As Long, NewData As String, Chr10Pos As Long
i = 1
NumStyle (i)

For Each c In Selection
c.Offset(0, 1).Value = NumStyle(i) & c.Value
NewData = c.Offset(0, 1).Value
i = i + 1
For Each str1 In Array(Chr(10))
Chr10Pos = InStr(1, NewData, str1)
If Chr10Pos = 0 Then
Else
Do Until Chr10Pos = 0
NewData = Left(NewData, Chr10Pos) & NumStyle(i) & _
Right(NewData, Len(NewData) - Chr10Pos)
i = i + 1
Chr10Pos = InStr(Chr10Pos + 1, NewData, str1)
Loop
End If
Chr10Pos = 0
Next str1
i = 1
c.Offset(0, 1).Value = NewData
Next c

End Sub

Function NumStyle(i As Long) As String

NumStyle = i & ") " 'Defines the numbering style

End Function
[/vba]

I do realise that my code has limitations. I have indicated the limitations in my workbook. I would welcome any suggestions to improve the code. Nonetheless, I believe it should be useful for some people.

Regards,
X10A

Re: Fast Typing With Keywords

Hi Carlmark n Jorge,

With regards to selecting from the List PopUp with keyboard, this is what I proposed:

1) Go to the Userform and view it's code. Inside you will find the following code:

[vba]
Private Sub LstAddress_Click()
GlbTargetRange.Value = Me.LstAddress.Value
Unload Me
End Sub
[/vba]

2) Replace with:

[vba]
Private Sub LstAddress_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyReturn
GlbTargetRange.Value = Me.LstAddress.Value
Unload Me
Case vbKeyEscape
Unload Me
End Select
End Sub
[/vba]

What this is trying to achieve is that
1) you can use the keyboard arrow keys (Up and Down) to select the records.
2) To make your selection, hit the Enter key.
3) If you decided not to make any selection, just press Esc and it will close the popup list.

Re: Fast Typing With Keywords

Hi carlmack,

Just like to say your work is cool! I think it is great for people doing lots of data entry.

Re: Bold Selected Text In A Column

Hi,

the following code should give you what you need:

Sub BoldNames()
    On Error GoTo errhandler
    
    Dim EditRng As Range, FixedList As Range, IntStart As Integer


    ' change range as appropriate
    Set EditRng = Range("A2:A8")


    ' change range as appropriate
    Set FixedList = Range("J2").Resize(Application.WorksheetFunction.CountA(Range("J:J")) - 1, 1)


    For Each n In FixedList
        For Each n2 In EditRng
            If IsEmpty(Application.WorksheetFunction.Find(n, n2, 1)) Then
            IntStart = Application.WorksheetFunction.Find(n, n2, 1)
            With n2.Characters(Start:=IntStart, Length:=Len(n)).Font
                .FontStyle = "Bold"
                .ColorIndex = 5
            End With
            End If
        Next n2
    Next n
Exit Sub


errhandler:
If Err.Number = 1004 Then
Resume Next
End If


End Sub

Re: Cell Address In A User Defined Function

Hi,

Maybe you can use the Find function? Type "=Application.CalculateFull" (without quotes), then click on "Find All". That should return all cells that uses this UDF.

Re: Sumproduct With Criteria

Hi,

Is it a must to use SumProduct? A pivot table will get what you want.

Re: Workbook Password Protect 2 Levels

You seem to be asking a new question, yes? Why not start another thread so that you can get the appropriate answers?