Posts by rdkalinowsky

Re: Capturing Data From Userforms Created At Run Time

Thanks! I'll give them a try. I'm still learning my way around some of these trickier things.

Re: Capturing Data From Userforms Created At Run Time

I'm not permitted to upload code from my office, but it looks similar to the code from Andy Pope's attachment on Page 1 in the aforementioned thread, but I modified it such that the number of label and textbox rows are passed via another process instead of read from a spin control. Can we use his code for sake of discussion?

'
' Create label and textboxes on the fly
'
    Dim lngIndex As Long
    Dim labTemp As MSForms.Label
    Dim txtTemp As MSForms.TextBox
    Dim sngLeft1 As Single
    Dim sngLeft2 As Single
    Dim sngTop As Single
    Dim sngHeight As Single
    Dim sngWidth1 As Single
    Dim sngWidth2 As Single
    Dim strLabName As String
    Dim strTxtName As String
    
    m_lngWeldCount = Number
    With Me.Frame1
        sngTop = 10
        sngHeight = 15
        sngWidth1 = 50
        sngLeft1 = 5
        sngLeft2 = sngLeft1 + sngWidth1 + 10
        sngWidth2 = .InsideWidth - sngLeft2 - 20
        
        For lngIndex = 1 To m_lngWeldCount
            strLabName = "labWeld" & Format(lngIndex, "000")
            Set labTemp = .Controls.Add("Forms.Label.1", strLabName, True)
            With labTemp
                .Left = sngLeft1
                .Top = sngTop
                .Width = sngWidth1
                .Caption = "Weld " & lngIndex
            End With
    
            strTxtName = "txtWeld" & Format(lngIndex, "000")
            Set txtTemp = .Controls.Add("Forms.textbox.1", strTxtName, True)
            With txtTemp
                .Left = sngLeft2
                .Top = sngTop
                .Width = sngWidth2
            End With
    
            sngTop = sngTop + sngHeight + 10
        Next
        If sngTop > .InsideHeight Then
            .ScrollHeight = sngTop
            .ScrollBars = fmScrollBarsVertical
        End If
            
        .Controls("txtWeld" & Format(1, "000")).SetFocus
            
    End With

Re: Capturing Data From Userforms Created At Run Time

That may work, but this concept is a bit over my head...any chance you might be able to provide a piece of code that demonstrates the task? I'm struggling with how to structure the block of code...are you saying to somehow define each control during the creation of the form, then read the array values from the controls independently? I'm a bit lost...:(

Hello,

I found a useful thread http://www.ozgrid.com/forum/showthread.php?t=36925&page=2 that was very helpful in showing me how to create dynamically scalable UserForms at run time.

I'm now stuck on the obvious Part 2 that isn't included...How does one capture the values in the text fields without knowing the names of those fields.

My initial thought is to use a For loop to read the values into an array, like so...

Dim ReadArray (TextFieldCount)
For k = 1 To TextFieldCount
     ReadArray(k) = ?????????
Next k

If it were a simple read from a text field I'd use something like:
Set ReadField = TextBox1.Value

Can anyone help me figure out how these autogenerated fields will be named? Thanks.

Re: Check If A Worksheet Is A Chart Sheet

Sometimes it's simpler than I make it...LOL! Thanks Andy!

Hi all,

I'm in need of an easy VBA way to determine if a worksheet is a chart sheet. I'm pulling data from multiple closed workbooks and then performing several operations (mostly numerical averaging) on the imported data. Each source workbook contains a mix of dozens of standard worksheets and charts. I'd like my code to import data from only the grid sheets and ignore the charts, as runtime errors would be generated.

The basic starting flow for each workbook is:

WorkbookCount = WorkbookCount + 1
Set wkbk = Workbooks.Open(vrtSelectedItem)
SheetCount = wkbk.Sheets.Count
For i = 1 To SheetCount
     ThisWorkbook.Activate
     Sheets.Add Before:=Sheets("Data")
     SheetName = wkbk.Worksheets(i).Name
     ThisWorkbook.ActiveSheet.Name = SheetName
     .
     .
     .
Next i
wkbk.Close

When a chart sheet is found in the source workbook, the "SheetName = ..." line generates a runtime error - I've deduced that it's because it's a chart sheet, as the code handles all grid sheets without incident.

I need to add an IF statement to test for a chart sheet. Any help? Thanks.

Re: Add A Space Before Every Cell Value

Perfect! Thanks...Saved about 90% of the processing time and about half of my file size!

OK, here's a unique one.

I'm trying to automate the importing and processing of a fairly large formatted text file (~15000 lines) containing many tables of data. I've been refining this process for several months and recently was alerted to a problem. A few of my tables have widths greater than 255 characters (309 to be exact).

My process involves opening the text file in Excel then performing a .Copy on the Worksheet into ThisWorkbook, naming the sheet "Source". I then perform up to 66 separate parse operations on the various tables depending on which options a user selected from a form.

The problem is that I assumed that the Copy Method would simply make a duplicate of that sheet in ThisWorkbook. Instead, it seems to have truncated any columns that are longer than 255 characters, leaving me with partial data in a few tables.

My workaround was to perform a TextToColumns on the text file prior to the import such that it would simply split the data into columns that could be reassembled on the other side. In doing so, I seemed to have stripped a leading <space> from each cell which was apparently put there previously. This is causing a lot of problems for my formatting code which also uses TextToColumns to separate the tables into columns. The simplest solution seems to be trying to add the spaces back in, assuming there isn't a better way to import this data in the first place.

Just prior to copying the sheet into ThisWorkbook, I'm using the following code after the TextToColumns to add the space back in, but it's taking an awfully long time and causing a noticable time delay in processing my code and adding considerable size to the finished workbook it creates. If someone could recommend a better solution, either to the import process or to replace this God-awful loop, I'd appreciate it. Thanks.

Application.ScreenUpdating = False
For i = 1 To 65535
   wkbk.Sheets(1).Cells(i,3).Formula = " " & wkbk.Sheets(1).Cells(i,1).Value
   wkbk.Sheets(1).Cells(i,4).Formula = " " & wkbk.Sheets(1).Cells(i,2).Value
   wkbk.Sheets(1).Cells(i,1).Formula = wkbk.Sheets(1).Cells(i,3).Value
   wkbk.Sheets(1).Cells(i,2).Formula = wkbk.Sheets(1).Cells(i,4).Value
Next i
wkbk.Sheets(1).Range("C:D").ClearContents
Application.ScreenUpdating = True

Also, I don't know how long the actual file will be, so I'm almost forced to assume 65535...

Re: Formula Entry Into Chart Title

OK, I'll try it. It'll require a little reworking from my original concept, but it seems like it's a good method. Thanks!!

Re: Formula Entry Into Chart Title

In this case I do need a macro, as this task is part of a large automation process where I'm creating dozens of worksheets and charts. I've been struggling with this chart titling for a few days now. It's frustratingly simple in the GUI but I can't get it to work in VBA...That said, you may have something there by creating the string in a cell first, then referencing it in the chart title...hmmm.

Hi all,

I've found several threads describing how to make a chart title dynamic by entering a cell reference into the formula bar with the chart title selected. I am wondering if anyone knows if it is possible to have VBA "code" a formula into a chart title that would be a concatenation of several strings.

Ideally, I'd like to specify part of the title from a variable, and another part from a user-specified range, as in the following snippet from a module of mine:

ChartSubtitleText = Sheets("Sheet1").Range("A5")


ActiveChart.ChartTitle.Select
Selection.Characters.Text = ChartTitleText & Chr(10) & ChartSubtitleText

where ChartTitleText is a string variable that is passed to the Sub.

This works fine and correctly builds the title block during execution of the macro, but I'd like to modify the capability to allow the user to enter a new value in Sheet1!A5 and have all of the charts update dynamically without re-executing the macro.

Is there a way to have VBA create the chart and build the Chart Title with a formula that will allow for dynamic updating? I've tried having it enter the following line into the formula bar, but get errors:

=CONCATENATE(ChartTitleText, CHAR(10), Sheet1!A5)

Any suggestions would be very helpful. Sorry, but I can't post more of my code for security reasons.

Hi all.

I've found some threads that describe how to build a hyperlinked list of worksheets as a Sheet Index.

My project has several "charts on new sheets" and I'd like to add them to the index as well in a separate column, B:B for instance.

Here is my code. It gives a bad reference, seemingly since there is no visible attach point for the HTML code. Any ideas? Thanks.

Rob

Dim cht As Chart
Dim rngLinkCell As Range
Dim strSubAddress As String
Dim strDisplayText As String


Worksheets("Index").Range("B:B").ClearContents
Worksheets("Index").Range("B1").FormulaR1C1 = "Charts"


For Each cht in ActiveWorkbook.Charts
     Set rngLinkCell = Worksheets("Index").Range("B65535").End(xlUp)
     If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1,0)
     strSubAddress = cht.Name
     strDisplayText = cht.Name
     Worksheets("Index").Hyperlinks.Add Anchor:=rngLinkCell, _
          Address:="", _
          SubAddress:=strSubAddress, _
          TextToDisplay:=strDisplayText
Next cht

Re: Inserting Several Images Into Separate New Slides

Thanks again, sir! We'll give this a go...

Re: Inserting Several Images Into Separate New Slides

A colleague of mine needs to use something identical to this algorithm, but is using PowerPoint 2000 SP3 and we're getting errors, likely do to version compatibility. Could someone suggest a version of Andy Pope's code above that will work in 2000? Thanks in advance.

Re: Combine Multiple Rows Into One Row

Figured it out. Whew...that only took all morning and about 150 test runs. For the benefit of future searchers, here's the code.

Dim i As Variant
Dim j As Variant
Dim SourceRowNum As Variant
Dim SourceColNum As Variant
Dim DestRowNum As Variant
Dim DestColNum As Variant
Dim BlockCount As Long


BlockCount = Range("A1").End(xlDown).Row
SourceRowNum = 0
DestRowNum = 0


For i = 1 To BlockCount / 10


     DestRowNum = DestRowNum + 1
     DestColNum = 1


     For j = 1 To 10


          SourceRowNum = SourceRowNum + 1


          For SourceColNum = 1 To 4
          
               Sheets("Output").Cells(DestRowNum, DestColNum).Value = Cells(SourceRowNum, SourceColNum).Value
               DestColNum = DestColNum + 1


          Next SourceColNum


     Next j


Next i

Re: Combine Multiple Rows Into One Row

Not exactly, jindon, but thanks for trying. Your code seems to copy the top 10 rows into columns E-H, then stops. I need something that will take A1:D10 and format them into A1:AN1, then take A11:D20 and paste them into A2:AN2, etc...I've attached a new file to show the input format and the desired output format. Any other ideas?

Re: Add Hours To Date And Time

Excel also will allow you to add numeric decimal days to any time field. To add one day, simply refer to the cell and add "1" to it. For the record, 47 hours is 1.958333 days, so try =A1+1.958333.

Re: Formula To Search List Of Numbers For 2 Vaules To Equal My Total

Cam,

Sounds like a solver issue. Would the solution(s) in this thread help you?

http://www.ozgrid.com/forum/showthread.php?t=40750

Rob

Re: Combine Multiple Rows Into One Row

bump

Re: Combining Multiple Rows Into One Row

Here's the above in Excel format...