Have Geek Brain

Merged cell content disabled outside of page break

I have a vb.net program that exports information to specific fields in an Excel workbook using named ranges. One of the named ranges is the notes section, which is disabled (not displayed or printed) if it goes beyond the page break. I'm looking for a way to dynamically create new named ranges to accommodate all of the annotation text.

Other information:

  • The named range is a merged cell from A2: L36 with text wrapping enabled

  • Column A is 31 inches wide

  • Columns B through L are 8.38 units wide.

  • All lines are 15.75 units high

  • I DO NOT want to print blank pages, change the font size, disable text wrapping, the end user does any re-formatting and does not resize the cells as I have tried these approaches without success.

  • Using Excel 2007

HELP PLEASE!

Thanks in advance! ~ Eighth

Pardon my ignorance, I am still n00b

0


a source to share


2 answers


Thanks again to Lunatik for your answer. I was able to find a suitable workaround before I see your last post. This is not the most elegant solution, but it is the most suitable for what I was trying to accomplish. I hope this might be useful to someone else in the future.

Dim sNotes As String = g_strProgram.sNotes
Dim iStartIndex, iEndIndex As Integer
Dim iRow As Integer = 0

If (sNotes.Length > 0) Then
   g_frmMain.ucAdditionalInfo.txtNotes.Text = sNotes
   Do
      iRow += 1
      oWorkSheet.Range("A" & iRow + 1 & ":L" & iRow + 1).Merge()
      iStartIndex = IIf(iRow = 1, 0, iEndIndex + 1)

      If (g_frmMain.ucAdditionalInfo.txtNotes.GetFirstCharIndexFromLine(iRow) >= 0) Then
         iEndIndex = g_frmMain.ucAdditionalInfo.txtNotes.GetFirstCharIndexFromLine(iRow) - 1
      Else
         iEndIndex = g_strProgram.sNotes.Length - 1
      End If

      oWorkSheet.Range("ProgramNote").Offset(iRow, 0).Value = g_strProgram.sNotes.Substring(iStartIndex, iEndIndex - iStartIndex).TrimEnd(vbCrLf)
   Loop While (iEndIndex < g_strProgram.sNotes.Length - 1)
Else
       oWorkSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden
End If


If anyone out there reads it requires clarification with any of the above, feel free to ask a question here! ~ Eighth

+1


a source


Aaargh! United cells is Satan's work and should be avoided at all costs. Even though their presence is considered benign, they can have subtle and cunning ways of destroying an otherwise healthy worksheet.

Are you not specifying if the text is cut vertically or horizontally?

You should probably take a look at a method AutoFit

to force the text to stay inside the printable area. This will resize the cell (s) to fit the text to the selected font size, and since the printable area is determined by the cell borders, this should ensure that all text remains visible after printing. In the meantime, there is no need to worry about it. ”



If that doesn't work, it might be difficult to establish a link without getting rid of the merged cells. Are they absolutely necessary?

I am working on the same worksheet template you have right now, and I have cells that are resized to fit a wide range of strings up to 911 characters using nothing more than normal auto-fit.

0


a source






More articles:


All Articles