By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,110 Members | 1,542 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,110 IT Pros & Developers. It's quick & easy.

Vb 6.0 - Spreadsheet Rows Has Hidden Data

P: 7
Hello members,
I need help with my spreadsheet display problem. I generated my spreadsheet in using vb6.
Everything is working except that when I display the display (making it visible), some of the data in the rows hides underneath the row lines. I have to physically go into the spreadsheet to expand the rows to see the hidden data. Also, if I print the spreadsheet, the hidden part of the spreadsheet is cut off unless I mannually expand the rows. How can I programmatically expand the rows to fit each amount of records in the row. Currently, I have a module that is mannually calculating the rows but I may be doing it wrong.
Can anyone help please?

Below is the code that calculates and formats the row presently:
Expand|Select|Wrap|Line Numbers
  1.    w = 0
  2.     For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
  3.  
  4.     rht = xlWksht.Range("A5").RowHeight
  5.  
  6. With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
  7.             .HorizontalAlignment = xlLeft
  8.             .VerticalAlignment = xlTop
  9.             .WrapText = True
  10.             .Orientation = 0
  11.             .MergeCells = True
  12.             .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
  13. End With
  14.  
Below is my entire module:
Expand|Select|Wrap|Line Numbers
  1.  
  2.     ii = 5
  3.      w = 0
  4.     For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
  5.  
  6.     rht = xlWksht.Range("A5").RowHeight
  7.  
  8.     'Do Until M.qBW.EOF = True
  9.      Do While Not M.qBW.EOF
  10.         ii = ii + 2
  11.         xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
  12.         xlWksht.Cells(ii, 2).Value = M.qBW![Description]
  13.         xlWksht.Cells(ii, 3).Value = ""
  14.         xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
  15.         xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
  16.  
  17.         SrchCriteria = "[Name]= " & "'" & M.qBW![Personnel2] & "'"
  18.         rsinPers.FindFirst SrchCriteria
  19.         If rsinPers.NoMatch = False Then
  20.            xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
  21.         End If
  22.  
  23.         SrchCriteria = "[Name]= '" & M.qBW![Personnel3] & "'"
  24.         rsinPers.FindFirst SrchCriteria
  25.         If rsinPers.NoMatch = False Then
  26.            xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
  27.         End If
  28.  
  29.         SrchCriteria = "[Name]= '" & M.qBW![Personnel4] & "'"
  30.         rsinPers.FindFirst SrchCriteria
  31.         If rsinPers.NoMatch = False Then
  32.            xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
  33.         End If
  34.  
  35.         SrchCriteria = "[Name]= '" & M.qBW![Personnel5] & "'"
  36.         rsinPers.FindFirst SrchCriteria
  37.         If rsinPers.NoMatch = False Then
  38.            xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
  39.         End If
  40.  
  41.         SrchCriteria = "[Name]= '" & M.qBW![Personnel6] & "'"
  42.         rsinPers.FindFirst SrchCriteria
  43.         If rsinPers.NoMatch = False Then
  44.            xlWksht.Cells(ii, 10).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
  45.         End If
  46.  
  47.         xlWksht.Cells(ii, 11).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
  48.         xlWksht.Cells(ii, 12).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
  49.         xlWksht.Cells(ii, 13).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
  50.         xlWksht.Cells(ii, 14).Value = M.qBW![End Date] & Chr(10) & M.qBW![End  Date]
  51.  
  52.         If M.qBW![Comments] = "" Or IsNull(M.qBW![Comments]) Then
  53.            mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
  54.         Else
  55.            mystr = "Comments:" & "'" & xlApp.Clean(Trim(M.qBW![Comments]))
  56.         End If
  57.  
  58.         Do
  59.             Pos = InStr(Pos + 1, mystr, ":")
  60.             If Not Pos = 0 Then
  61.                If Mid(mystr, Pos - 5, 1) = "/" Then
  62.                    mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
  63.                    Pos = Pos + 2
  64.                End If
  65.             End If
  66.         Loop While Not Pos = 0
  67.  
  68.  
  69.         xlWksht.Cells(ii + 1, 1).Value = "Comments:"
  70.         xlWksht.Cells(ii + 1, 2).Value = Mid(mystr, 11)
  71.  
  72.         With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
  73.             .HorizontalAlignment = xlLeft
  74.             .VerticalAlignment = xlTop
  75.             .WrapText = True
  76.             .Orientation = 0
  77.             .MergeCells = True
  78.             .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
  79.         End With
  80.  
  81.         xlWksht.Columns("A:A").ColumnWidth = 9.15
  82.  
  83.         'Draw Underline after each record:
  84.         '---------------------------------
  85.         TStr = "A" & CStr(ii + 1) & ":N" & CStr(ii + 1)
  86.         xlWksht.Range(TStr).Select
  87.         If Not IsEmpty(Selection.Range("A1")) Then 'check if first cell is empty
  88.            With xlWksht.Range(TStr).Borders(xlEdgeBottom)
  89.            .LineStyle = xlDouble
  90.            .Weight = xlThin
  91.            .ColorIndex = xlAutomatic
  92.            End With
  93.         End If
  94.  
  95.         M.qBW.MoveNext
  96.     Loop
  97.  
Thanks.
GiftX
Oct 5 '07 #1
Share this Question
Share on Google+
5 Replies


kadghar
Expert 100+
P: 1,295
Hello members...


Below is the code that calculates and formats the row presently:
Expand|Select|Wrap|Line Numbers
  1.    w = 0
  2.     For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
  3.  
  4.     rht = xlWksht.Range("A5").RowHeight
  5.  
  6. With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
  7.             .HorizontalAlignment = xlLeft
  8.             .VerticalAlignment = xlTop
  9.             .WrapText = True
  10.             .Orientation = 0
  11.             .MergeCells = True
  12.             .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
  13. End With
  14.  
...
GiftX
Hi, i think something like this should work:

xlwksht.columns.autofit

you can add it to the with, just make sure you do it after all the data has been added to your sheet.

HTH
Oct 5 '07 #2

P: 7
Hello HTH,
thanks for your contribution. But my problem is not column width. Is there any way I could attach the spreadsheet so you can see exactly how it looks.

I populated my spreadsheet with input from access files. Because each record is too long to fit in my spreadsheet, I split each record into two lines. The first line prints on the first cells and the second line prints on the next row of merged cells this second row is the one that has the hidden data, so it is not a ColumnWidth issue. Do you understand what I'm saying?
If you need more clarification, let me know.
Thanks.
GiftX.
Oct 8 '07 #3

kadghar
Expert 100+
P: 1,295
Hello HTH,
thanks for your contribution. But my problem is not column width. Is there any way I could attach the spreadsheet so you can see exactly how it looks.
GiftX.
yes, just write your post, then edit it, and while editing you'll have the chance to manage attachments
Oct 8 '07 #4

P: 7
Hello honorable members,
I want to include an attachment of my spreadsheet so you can see exactly how some data within the rows are are hidden.

However, I can see any option to manage attachments.
GiftX.
Oct 15 '07 #5

kadghar
Expert 100+
P: 1,295
Hello honorable members,
I want to include an attachment of my spreadsheet so you can see exactly how some data within the rows are are hidden.

However, I can see any option to manage attachments.
GiftX.
once again.

Post your message, and after you have posted it, you'll have 1 hour to edit it. While editing it, you'll be able to manage attachments.
Oct 15 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.