Posts by turbonate

I have several bubble plots which I'm using the bubble size to show one of three strategic scores. In many cases the score for the Z-Axis (size) will be Zero, so that point doesn't show up on the chart. How can I set the Z-Axis scale of a bubble plot to show Zero's as the smallest bubble size rather than one's as the smallest bubble size?

I've tried this approach: http://peltiertech.com/Excel/Charts/ControlBubbleSizes.html
with the smallest value being -1 or -5. Didn't work.

Using Excel 2013, Windows 7.

Re: Go to other cell based on matching values using button control

Using the two find Methods listed on Ozgrid you can add this code to the button sub.

Find method reference: http://www.ozgrid.com/VBA/find-method.htm
Find Date reference: http://www.ozgrid.com/VBA/find-dates.htm

Public Sub highlightCell()    
    Dim strName As String
    Dim strDate As Date
    Dim rName As Range
    Dim rDate As Range
    
    'get parameters
    strName = Range("B1").Value
    strDate = Range("D1").Value
    
    'format date
    strDate = Format(strDate, "Short Date")
    
    On Error Resume Next
    
    'find strName location in column 1 and set to rName range
    Set rName = ActiveSheet.Columns(1).Find(What:=strName, After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    
    'quit if not found
    If rName Is Nothing Then
        MsgBox "Can't find the selected Name"
        Exit Sub
    End If
    
    'find dtDate location in row 3 and set to rDate range
    Set rDate = Rows(3).Find(What:=CDate(strDate), After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    
    'quit if not found
    If rDate Is Nothing Then
        MsgBox "Can't find the selected Date"
        Exit Sub
    End If
    
    On Error GoTo 0
    
    'select the cell which corresponds to both Name and Date
    Cells(rName.Row, rDate.Column).Select
End Sub

Limitations: this code assumes that the sheet which you are editing will always be active while it is executing. We'd have to add more references to update it.

cheers!
Nate

Re: Running Multiple Macros on Different Cell Changes

you could also pass the target range to the sub:

Private Sub Worksheet_Change(ByVal Target As Range)      
    Select Case Target.Address 
        Case "$F$6", "$F$13", "$F$14": AutoResize
        Case "$C$18:$C$56": AutoFilter(Target)
        Case Else: Exit Sub 
    End Select 
     
End Sub 


Private Sub AutoFilter(Target as Range) 
    Target.CurrentRegion.AutoFilter Field:=2, Criteria1:="<>#N/A" 
End Sub

You might try AAE's suggestion as I didn't look up the AutoFilter function, I'm just suggesting another way to reference the cell you're working with. It might make more sense to pass the Target to the AutoResize() sub than the AutoFilter() sub. What you have will work, it's just a matter of if one of these ideas will work better.

Re: Running Multiple Macros on Different Cell Changes

Hi SF007,

will this work?

Private Sub Worksheet_Change(ByVal Target As Range) 


    Select Case Target.Address 
        Case "$F$6", "$F$13", "$F$14": AutoResize 
        Case "$C$18:$C$56": AutoFilter 
        Case Else: Exit Sub
    End Select 


End Sub

There is only one Worksheet_Change event which is triggered when a change occurs. You already captured the event that you want for AutoResize and for AutoFilter so there's no reason to have another event sub. You have the right structure already set up with a Case structure so you just need to add another Case which triggers the AutoFilter event. You were so close already!

Also, there is no such thing as Worksheet_Change1() event.

cheers!

Re: file system object error, trying to index a file list object

OK, so this function works, but it crashes excel due to the slow speed of the loop in each use of the function. (think folder with 200 files) this would be around 30,000 executions.

Since it is a function it runs each time excel decides to update sheets. We are violating one of the Golden Rules (Don't write functions/sheets that require xlCalculationManual).

I'm trying to think of a faster way to do this, is there some sort of replacement or workaround for indexing in the files collection?

Re: file system object error, trying to index a file list object

Hi Jindon!

That makes sense with what I am seeing in the locals window while running this script. Instead of an object with indexes I'm seeing separate entries name "item 1" "item 2" etc. It seems so close but yet so far.

Also, this is supposed to be a function but I modified the code to be a sub so that the built in error trapping and debug features work. This is my final code:

Public Function GETFILENAMES(fileFolder As String, index As Long)    
    '===================================================================================
    'this function returns the file name from the index position in fileFolder
    'macro created by Nathan Kaemingk on 12/14/12
    'with the help of user "Jindon" from Ozgrid.com
    '===================================================================================
    
    Dim myFileSys, myFolder, myFiles, thisFile 'file system vars
    Dim iFiles As Integer 'file collection index
    
    Set myFileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = myFileSys.getfolder(fileFolder)
    Set myFiles = myFolder.Files
     
    If myFiles.Count < index Then
        GETFILENAMES = CVErr(xlErrNA) 'index error
        Exit Function
    End If 'myFiles.Count < index
     
    For Each thisFile In myFiles 'using loop since indexing isn't possible for Files collection
        iFiles = iFiles + 1
        If iFiles = index And Left(thisFile.Name, 1) <> "~" Then 'exclude temp files
            GETFILENAMES = thisFile.Name
            Exit Function
        End If
    Next 'thisFile
End Function

cheers!
Nate

Re: file system object error, trying to index a file list object

Hi Jindon,

Thanks for the reply. I'm seeing the script error out when index = 1 and myFiles.Count = 200 so I don't think it's an index range issue... unfortunately, because your solution would work then!

Nate

Hey gurus,

I'm trying to make a function which will allow me to input a folder and an index in a sheet to quickly list files in my worksheet. I think my error has to do with not understanding the file system object but that's about as far as I can figure. I've modified my code to be a sub so that the error trapping works, I tried to comment and explain where the code will be modified back into a function once I figure out this error.

My code throws a runtime error 5: invalid procedure call or argument on myFiles(index).

here's my code:

Public Sub GETFILENAMES_func() 'fileFolder As String, index As Long)
'for testing
Dim fileFolder As String
Dim index As Long
fileFolder = "D:\Users\ln255\Documents\31121010\tuning_guide"
index = 1


'real function starts here
Dim myFileSys, myFolder, myFiles, thisFile 'file system vars


Set myFileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = myFileSys.getfolder(fileFolder)
Set myFiles = myFolder.files


If myFiles.Count < index Then
GETFILENAMES = CVErr(xlErrNA) 'index error
Exit Sub
End If


Set thisFile = myFiles(index) 'error out here


GETFILENAMES = thisFile.Name
End Sub

cheers!
Nate

Re: Highligh a cell on same row as active.cell whenever active.cell changes

Try using the worksheet_selectionChange event like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    'test for select column and range
    If Target.Column >= 2 And Target.Column <= 4 _
        And Target.Row >= 2 And Target.Row <= 22 Then
        
        'clear previous formatting
        With Range("S2:S22").Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With


        'highlight cell
        Cells(Target.Row, 19).Interior.Color = 65535
    End If
End Sub

cheers!
Nate

Re: OnKey gives all capital letters?

rory, thank you for the clarification. Glad to know it's a "feature", not a bug.

royUK, the point of my code is a bit beyond what I want to explain right now. I'll find a workaround for this section, if and when I get stuck I'll post again. I just don't want to use up valuable Guru time for sections that I know I can figure out myself. I really appreciate the willingness of people on this forum to help and I don't want to abuse their time.

cheers!

Hey gurus,
I'm trying to use Application.OnKey commands for upper and lower case letters, but it seems like Excel always uses upper case letters (chars 65 to 90) rather than using lower case letters (chars 97 to 122).

To be more specific: When I activate OnKey calls for all letters (65:90 and 97:122) and I press a lower case letter such as "e" I should get the character number 101, but instead I get the character number 69.

Can anyone shed some light on this?

Public Sub KeyEventOn()
    Dim iCount As Integer 'index for ASCII characters
    For iCount = 65 To 90
        Application.OnKey "{" & iCount & "}", "'MySub """ & iCount & """'"
    Next
    For iCount = 97 To 122
        Application.OnKey "{" & iCount & "}", "'MySub """ & iCount & """'"
    Next
End Sub




Public Sub KeyEventOff()
Dim iCount As Integer 'index for ASCII characters
    For iCount = 65 To 90
        Application.OnKey "{" & iCount & "}"
    Next
    For iCount = 97 To 122
        Application.OnKey "{" & iCount & "}"
    Next
End Sub


Public Sub MySub(ByVal KeyCode As Long)
    MsgBox KeyCode
end sub

Re: Best/fastest way to reference a cell: .cell vs .range

Quote from AAE;633876

Range Object - Object Model

Found a line in the Object Model:

Quote

Although you could use Visual Basic string functions to alter A1-style references, it's much easier (and much better programming practice) to use the Cells(1, 1) notation.

Thank you for the links in your reply. This is what I needed to know.

Cheers!
Nate

Re: It's possible to create an "autorization" workbook with excel?

I made a system like this before but don't have any of the code anymore. :sad:

My system assumes a shared file drive which all people are working on. After an engineer creates a Work Order the Work Order sheet is saved in a "ToBeApproved" folder on the shared drive and an email sent to the supervisor. When the supervisor gets the email s/he can open the file and press "Approved" or "Rejected". When the sheet is approved it is moved to another folder "ApprovedWorkOrders". If the "Rejected" button is pressed the sheet creates another email reply to the engineer which says the Work Order was rejected and leaves space for the supervisor to finish the email with reasons why.

Would a system like this work for you? If so what parts of the coding do you need help with?

cheers!
Nate

Hey gurus,

I found this: VBA Golden Rules #19: Use Named Ranges Over Cell Addresses. here: http://www.ozgrid.com/forum/showthread.php?t=76234

I'm guessing that means the .cells function is a subroutine of the .range function. Therefore

dim iRow as integer 'row indexiRow = 3with mySheet  .Range("B" & iRow)end with 'mySheet

should be faster than

dim iRow as integer 'row indexiRow = 3with mySheet  .cells(iRow , 2)end with

The string operator ("B" & iRow) to get the named range function seems like it would take longer than using the numerical references in .cells().

Thanks!
Nate

Re: Get worksheet buttons to execute code stored in an add-in module

I found an answer to this question myself eventually. By creating a worksheet control button via VBA it is possible to set the .OnAction parameter to a script which is in the add-in rather than using the button1_click() event in the worksheet. See my code bellow which checks if the button exists and then creates the button with the correct link if the button does not exist.

Public Sub QuoteSheetActivate()




    'if btnVendorCenter doesn't exist, create button and then add code to button
    Dim oBtn As Object 'button object
    Dim bBtnVendCentExists As Boolean 'bit is true if vendor center button exists
    
    bBtnVendCentExists = False 'guilty until proven innocent
    For Each oBtn In Worksheets(strQuoteSheetName).Buttons
        If oBtn.Name = "btnVendorCenter" Then bBtnVendCentExists = True 'button already exists
    Next
    'add Vendor Center button if its not already a button
    If Not bBtnVendCentExists Then 'add Vendor Center Button
        ActiveSheet.Buttons.Add(964.5, 36.75, 102.75, 21.75).Select
        Set oBtn = Selection
        With oBtn
            .OnAction = "editVendorCenter"
            .Name = "btnVendorCenter"
            .Characters.Text = "Vendor Center"
            '.Parameter = True
        End With
        With oBtn.Characters(Start:=1, Length:=13).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 1
        End With
        'select something else
        Range("C4").Select
        
    End If 'bBtnVendCentExists




End Sub

Cheers!
Nate

Re: Use 4th button in msgBox for ignore with vbYesNoCancel

Hey guys,

I kind of figured that the UserForm was going to be the answer. I have seen some example code of updating the API and I'm really not interested in doing that at all.

Thanks for the confirmation!
Nate

Hey gurus,

I've read in a few places that it is possible to use up to FOUR buttons in the msgbox API. I am trying to figure out how to use that fourth button but I don't see how to do the sum value for the "buttons" argument in the function call. I'm trying to use the vbYesNoCancel with an additional vbIgnore button option.

here's my code and what I'm trying to do:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Dim intAction As Integer 'User response to prompt when leaving
    Dim sh As Worksheet 'VendorCenter Worksheet
    
    'set objects
    Set sh = ThisWorkbook.Worksheets("VendorCenter")
    
    'why you leave?
    intAction = MsgBox("You are edding Vendor Center settings which should not be left open." & vbNewLine & _
        "Would you like to save and close?" & vbNewLine & vbNewLine & _
        "Please click Yes to save and close," & vbNewLine & _
        "Click No to leave without saving" & vbNewLine & _
        "Click ignore to leave Vendor Center open (not recommended)" & vbNewLine & _
        "Or click Cancel to go back to Vendor Center", vbYesNoCancel, "Leaving Vendor Center")
    Select Case intAction
        Case vbYes
            Me.IsAddin = True
            Me.Save
        Case vbNo
            Me.IsAddin = True
        Case vbIgnore
            'null
        Case vbCancel
            sh.activate
        Case Else
            MsgBox "Invalid Choice, going back to Vendor Center"
            sh.activate
    End Select 'intAction
End Sub 'Workbook_WindowDeactivate

I can make a user form if needed, but I want to know if this fictitious fourth button is usable before I go through the trouble.

cheers!
Nate

Re: program multiple elements of a multi dimension array simultaneously

That's EXACTLY what i'm looking for!

Jindon, you're Legit! thank you.

Is there any way to do this part of code without calling out each element individually?:

Dim aSequence(7, 1) As Integer
    
    'setup Evaluation sequence
    aSequence(0, 0) = -1
    aSequence(1, 0) = 0
    aSequence(2, 0) = 1
    aSequence(3, 0) = 1
    aSequence(4, 0) = 1
    aSequence(5, 0) = 0
    aSequence(6, 0) = -1
    aSequence(7, 0) = -1
    aSequence(0, 1) = 1
    aSequence(1, 1) = 1
    aSequence(2, 1) = 1
    aSequence(3, 1) = 0
    aSequence(4, 1) = -1
    aSequence(5, 1) = -1
    aSequence(6, 1) = -1
    aSequence(7, 1) = 0

I know how to use the array() function to program multiple elements of a variant as a single dimension array, but I'd like to be more explicit and I'm not sure how to do it in two dimensions. This kind of array programming is easy in languages like Matlab etc, but there must be some easy way to do it in VBA.

In Matlab this would look something like this:

aSequence = [ 
-1, 0, 1, 1, 1, 0, -1, -1;
1, 1, 1, 0, -1, -1, -1, 0;
]

[TABLE="width: 40"]

[TD="width: 20, align: right"][/TD]
[TD="width: 20, align: right"]cheers![/TD]

[/TABLE]
Nate

Re: Get worksheet buttons to execute code stored in an add-in module

Venkat, that would work but I'm trying to do this without a macro in the data sheet. I would like to only have code in the addin and not have to write any links in the data sheets.

Is this possible?

example: I have a "doIt" button in data123.xlsx as well as data456.xlsx and any variation of file names
I have and addin called myAddin with subs mySetup() and myDoIt() etc.
I'd like to have the code in mySetup() link to the button "doIt" if it exists in any of the data sheets (data123.xlsx or data345.xlsx)

does my trivial example help?

thanks for the reply!
Nate