Posts by brgipple

Hello

I have a question about the graphics used in VBA. I have created a spreadsheet that uses Command Buttons, Text Boxes and Mutli Page User Forms and all the other a sorted items found in VBA. Well, the Graphics of these items looks a little bit dated as to what is currently available with programs for developing UI's.

I was wondering if there is a update for VBA that has the latest toolbox for the UI's like what is available in C# programming? Or, does a person have to update to using Visual Studio to get the these updates?

If we need to switch to Visual Studio, which package will do the best job for updating Excel and Windows VBA code?

I see that Visual Studio 2005 Standard and Professional versions do not claim to support Microsoft Office Development. The only one that I found is Visual Studio Tools for Office, But this package does not contain all the current programming languages that the other packages do (J#, C++ ...).

Anyone have experience with Visual Studio 2005? Care to share which one will do the job on Excel development. Seems like a lot of money for the Office tools version (around 600+) for something that hopefully will be in the other cheaper verions, Well relatively cheaper anyway. :0)

Thank you for your time

Brian G.

Oh, by the way I write my code in Office 2003.

Hello all i have a question about the Setting of a range and then looking at that. I have the following code and it seems there should be a more elquent way to write it.

i want to do the 'ELSE' statement if the string in CurrentColumn(7) is found anywhere in row 1.

I tried:
if Not Columnfind then, but this gives me an error.

Set ColumnFind = ActiveSheet.Rows("1:1").Find(CurrentColumn(7), LookIn:=xlValues)
    If ColumnFind Is Nothing Then
    Else
        ColumnFind.EntireColumn.Copy
        Worksheets("Neg Bump").Select
        Columns("A:A").Select
        ActiveSheet.Paste
    End If

any suggestions?

Brian

Re: Column Letter instead of number

To all reading this, after i posted my thread another thread popped up at the top as a related link that i did not find in my search. this new thread had the answer that i was looking for.

ColumnLetter = Left(ColumnFind.Address(True, False), InStr(1, ColumnFind.Address(True, False), "$") - 1)

i didn't know about the 'ADDRESS' extension. Now i do, i LEARNED something today i can go home!

thanks for your time

brian.

Hello

is there a way to make the following code return the letter of the column instead of the number? currently if the 'String' value that is in 'ColumnFind' is in column B this code returns a value of 2. i Need the 'B' for later code to work.

ColumnLetter = ColumnFind.Column

i'm sure the is something small that i'm missing.
thank you

Brian

Re: Type mismatch using Find statement

Roy

Thanks the new code worked. Any idea why i was getting the 'Type Mismatch' error? Would like to know so i can avoid future issues and having to ask for help.

Thanks again

Brian G.

Re: Type mismatch using Find statement

Roy
I'm still getting the 'Type Mismatch' error when I run the code you modified for me. What the find function will be looking for is letter number combination like: ABC12345, this is what our PC logon's look like.

Thank you for your help
Brian

I'm using a find statement to locate a text string in a column but I get a 'type mismatch' error when I run it. I'm guessing that it's because of what I'm trying to find is a text and it is looking for a number. Anyway to point to the row number of where I find the macthing text in the column?

'Dimension variables used in Macro.
    Dim Logon As String
    Dim Level As String
    Dim RangeFind As Range
    Set RangeFind = Nothing
    
On Error GoTo Error
    
'Start Access verification
    Logon = Environ("USERNAME")
    Worksheets("Main").Select
    Set RangeFind = ActiveSheet.Columns("S:S").Find(What:=Logon, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If RangeFind Is Nothing Then
        MsgBox "You do not have Access to this WorkBook / WorkSheet", vbOKOnly, "No Access!"
        Set RangeFind = Nothing
        Exit Sub
    End If
    RangeFind.Activate
    Level = ActiveCell.Offset(0, 2)

the above code works great when what i'm trying to find is a number

thank you
brian

Is there a spell check in VBA? This would be for checking the code you write.

Thanx

Re: Length of number in a variable

Once again Andy Pope is the Man!

Thank You

Hello All

I am writing code that looks at entered employee numbers, but what i need to do is make sure there are 5 numbers in the variable (Number) i.e 12160. i tried using the

If Len(Number) <> 5 Then

But all i get is 2 because number is defined as an integer.

any idea's

thank you
Brian G.

Re: Free Floating a Chart

Thanx Andy worked great!

Brian G

Re: Free Floating a Chart

Andy

Sorry, I miss spoke earlier about the trendline datalabels, I ment to say the trendline equations that you can show on the chart. After I load all my data into the Sheet, set my formulas, create my chart and then put my Equations where i want them on the chart, if I manually change some of the data the Equations will move all over my chart. Is there a way to anchor the equations to certain spot on the chart?

I was going to attach a sheet out of my WorkBook so you could see what was happening, but the size is bigger than 48k (around 2M). can I send this to you? And if so how do you want it sent?

As for the Immediate Window, i had the Workbook open that would have had all the charts. and i got the message that showed earlier.

Brian G.

Re: Free Floating a Chart

Andy

The code change you gave me worked for my second series of data showing up in my chart, Thanx

I tried the code you gave to use in the immediate window (CTRL+G) and i got a alarm "reference is not valid", not sure what that means in this window.

Also on my chart i want the data labels for my trend lines to stay where i put them, they will until i start to manually change a input to the chart, is there a way to float or anchor the datalables where i put them. I tried the following code, but alas it did not work. When I looked at the formatable items for a DataLabels i didn't see anything for doing this like you can for a chart, is it possible to do?

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
            Selection.Left = 15
            Selection.Top = 285
    With ActiveChart.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
            .Placement = xlFreeFloating
    End With

Thanx for your time and effort

Brain G.

Hello everyone
I have question on how to get a chart to free float on my page. i have the following code and i added a section to try and do this but i can't seem to get it to work. any idea's?

Charts.Add
            ActiveChart.ChartType = xlXYScatter
            ActiveChart.SetSourceData Source:=Sheets("Neg Bump").Range("D2:E" & NumOfRows), PlotBy:=xlColumns
            ActiveChart.Location Where:=xlLocationAsObject, Name:="Neg Bump"
            ActiveChart.Axes(xlValue).Select
            With Selection
                .TickLabelPosition = xlHigh
            End With
                Selection.TickLabels.AutoScaleFont = True
            With Selection.TickLabels.Font
                .ColorIndex = 41
            End With
                ActiveChart.Axes(xlCategory).Select
            With Selection
                .TickLabelPosition = xlHigh
            End With
                Selection.TickLabels.AutoScaleFont = True
            With Selection.TickLabels.Font
                .ColorIndex = 41
            End With
            With ActiveChart
                .HasTitle = True
                .HasLegend = False
                .ChartTitle.Characters.Text = "Negative Bump"
                .Axes(xlCategory, xlPrimary).HasTitle = True
                .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Neg Bump Raised to Power"
                .Axes(xlValue, xlPrimary).HasTitle = True
                .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Delta Pitch"
            End With
            ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=True).Select
            ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select
            'With ActiveChart.ChartObjects
            '    .Placement = xlFreeFloating
            '    .PrintObject = True
            'End With
            ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
                Selection.Left = 50
                Selection.Top = 270
            ActiveChart.SeriesCollection(1).Trendlines(2).DataLabel.Select
                Selection.Left = 330
                Selection.Top = 270

I start my code in 1 workbook, this code will open a file in another workbook so i can copy information to my main workbook, i would like to close the 2nd workbook once i'm done with it.

'Load the CSV file from the selected path..
    Workbooks.Open Filename:=CSVfile, Origin:=xlWindows
    If IsEmpty(Range("A2")) Or IsEmpty(Range("AG2")) Then
            Message = "The CSV was not a FULL CSV file."
            Message = Message & "Plaese use the ADHOC Query to obtain a full file"
            MsgBox Message, vbOKOnly, "Problem with CSV file!"
            Quiting = True
            Exit Sub
    End If
    Cells.Select
    Selection.Copy
    Set Sourcebook = ActiveWorkbook
    Workbooks("Calculate_XP.xls").Activate
    Worksheets("Import Sheet").Select
    Range("A1").Select
    ActiveCell.PasteSpecial
    Cells.Select
    With Selection.Font
        .Name = "Comic Sans MS"
        .Size = 8
    End With
    Columns("A:AZ").AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    'Workbooks(Sourcebook).Activate
    'ActiveWindow.Close

i can't seem to get the last 2 lines to work. i have set 'sourcebook' to be a workbook at the beginning of the macro.

any thought would help

Thanks Brian G.

Hello

I am wanting to select a range of cells in a column but the start point and end point will be different each time, The Column will be a Known each time though. I have the following code and wondering how to adapt it to do what i want.

Range("D2:D" & NumOfRows).Select

I want to make the '2' of D2 a variable(StartOfRows). I know i'm just missing something simple.

Thank you all for youe help

Brian G.

Think i already know the answer to this, but can you use letters instead of numbers in a FOR-Next-LOOP? Tried to get it to work a couple of different ways but couldn't get it, thought i would ask.

FOR I = A to J
CODE'
Next I

Thnak you

I wan to delete a sheet from my workbook, and i get a question when my code deletes the sheet

here is my code:

Sheets("Neg Bump").Delete

How can i have my Macro answer the question:

'The selected sheet(s) will be permanitly deleted

to delete click 'yes'
to not delete click 'no'

Thank You

I have the following code that will open a txt file into a new workbook and then copy that workbooks cells into the workbook that i'm running. When i close the new workbook i get the question 'there is a large amount information on the clipboard, do you want to be able to paste it later? "YES", "NO" or "CANCEL".

how can i get VB to answer 'yes' to that question automatically?
or is there another way to close that workbook and after i have pasted the information?

at the moment i have the workbook.close line commented out.

Dim Sourcebook As Workbook


10
    Filter = "All Files (*.*), *.*"
    DATfile = Application.GetOpenFilename _
        (filefilter:=Filter, _
        filterindex:=1, _
        title:="Select DAT file to import")
    If DATfile = False Then
        YeahImSure = MsgBox("No File was selected. Press ReTry to try again.", vbRetryCancel, "Please select file")
        If YeahImSure = vbCancel Then
            Exit Sub
        End If
    End If
'Prompt which file you selected
    msg = "You have selected the following file to be imported: " & DATfile & vbCrLf & vbCrLf
    YepDone = MsgBox(msg & "Is this the correct DAT file?", vbYesNo, "Import File check")
    If YepDone = vbNo Then
        GoTo 10
    End If


'Load the DAT file from the selected path..
    Workbooks.Open Filename:=DATfile
    Cells.Select
    Selection.Copy
    Set Sourcebook = ActiveWorkbook
    'ActiveWindow.Close
    Workbooks("Calculate.xls").Activate
    Worksheets("Import Sheet").Select
    Range("A1").Select
    ActiveCell.PasteSpecial

Thank you
Brian G.

[FONT=Comic Sans MS]I have the following code and I want to extract the RSquared value off the chart to do futher calculations. Any idea's on how to do this?

'Make Scatter plot chart for sheet 'Load Power'
    Sheets("Load Power").Select
    Charts.Add
        ActiveChart.ChartType = xlXYScatter
        ActiveChart.SetSourceData Source:=Sheets("Load Power").Range( _
            "D2:D5200,E2:E5200"), PlotBy:=xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Load Power"
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Load Power"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Load Power"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Delta Gram"
        End With
        ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
            Backward:=0, DisplayEquation:=False, DisplayRSquared:=True).Select
        ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
        Selection.Left = 361
        Selection.Top = 282

Thank you,
Brian G