How to test for an empty drawing textbox

[Walter349]

I have a report that is consolidating information from several excel forms, which all have four drawing textboxes.

This is performed by selecting all relevant reports using 'Appplication.getopenfilname'. Filtering on .xls and allowing multiselect

These textboxes are supposed to be completed with sumary information(Text), but often are empty.

I have the following piece of VBA which does the job, but if the textbox is blank, it will simply move on causing the columns to be out of sequence

I want to test for the textboxes being empty and if yes put in a default, like "NTR" or something

This is the code that sort of works;

rng is source workbook range
Sh1 is source worksheet & textbox
rng1 is destination workbook & cell

Set rng = sh1.textboxes("text box 3")
set rng1 = sh.cells(Rows,Count, 1).End(xlUp) (2)
rng.copy
Rng1.pastespecial xlValues

This is what I have tried without success

Alt2 = "Nothing reported"

set rng = sh1.textboxes("Text Box 3")
 If rng = "" then
   set rng1 = sh.cells(Rows,Count, 1).End(xlUp) (2)
   rng.value = Alt2
   rng1.pastespecial xlValues
 else
   set rng1 = sh.cells(Rows,Count, 1).End(xlUp) (2)
   rng.copy
   rng1.pastespecial xlValues
 end if

This presents me with an error 438 ' Object does not support this property or method.

I have no doubt that there is much better way of doing this, but I am interested in finding out why this does not work. What am I doing wrong?




'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Hi,

A Textbox is a SHAPE, not a RANGE.

Dim shp As Shape, rng1 As Range
Set shp = Sheet1.Shapes("tbx1")
Set rng1 = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp)(2)
shp.Copy
rng1.PasteSpecial xlValues

But beyond that, WHY would you use a TxetBox for a form? Much easier and simper to use cells with native Excel functionaliy. Much easier for the USER and much easier on the DEVELOPER.

What are your requirements?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Hi, Skip

At first it was several ranges of merged cells, providing a fairly large area for summary and other information. but for some reason, that I was never able to determine. Some of the report users were losing information at the begining of each merged cell group, others were not. The simplest fix as I was never able to understand the cause was to replace the merged cell groups with text boxes(I don't think I mentioned that these are drawing textboxes), which has been working well.

Using the range statement for the text boxes has also functioned but when trying to meet the need to consolidate the report summarys, I ran into the problem of empty textboxes. When consolidating the reports any empty text boxes were ignored and that resulted in the cell being populated by the relevant textbox content from the next report, bringing the information out of sequence with the rest of the information in the row

I understood, poorly, that the drawing textboxes had to be handled differently.

I am not at my desk at the moment, but will try your solution tomorrow.

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Merged Cells have their own set of problems, and I would urge a novice to avoid using them. But I would ALSO urge you to avoid using textboxes, when native Excel Sheet features would be more than adequate.

If you declare your variables as you described, YOUR code will ERROR.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Skip,

I am not clear on what you are suggesting regarding 'native Excel Sheet features'. could you clarify that?

The report form is a standard spreedsheet, configured to represent a form that is completed by the users. Who enter data from cell located, drop down boxes in most areas, but also enter freetext information in others and of course the textboxes under discussion.

On completion of the report, they press a command button that then automatically emails it to a select set of addresses.

If using merged cells is not reccomended and text boxes are not a good idea, then how best to present the data entry area to the user for what could be a detailed amount of free form informaton? (When they bother to put anything in that is).

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Native Excel form features:

Cell Locking
Sheet Protection

Enables the user to TAB from one data entry cell to the next and PREVENTS the user from tampering with cells that contain instruction, labels, formulas etc.

You could have logic that prevents the user, via VBA, from proceeding UNTIL a valid entry is made.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Ah! I see,

Cell locking and sheet protection are used on this report form. Allowing data entry on to the required fields.

There are no controls, as you have defined, other than protection. As not all the areas included in the report are relevant to all the locations being reported on. this is a one size-fits-all format.

Without the merged cells or textboxes, how would you suggest that a defined area for entry of the summary free form text be presented? So that users are clear where it has to be entered.

Bear in mind that the reason for all of this, is that left to make their own decisions (the users) on anything, results in chaos.



'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Without the merged cells or textboxes, how would you suggest that a defined area for entry of the summary free form text be presented? So that users are clear where it has to be entered.

Well, you give instructions regarding what gets entered in what cell.

The user can ONLY select the cells you designate.

Give me some examples of what might be entered in "the summary free form text."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Skip,

Tried the changes you suggested, but now it does not pick up any of the textbox text at all.

How do I attach a file to this thread? Then I can send you a copy of the XL form, which might make things clearer



'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

Ah never mind, found it.



'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Cannot open you file at

http://www.tek-tips.com/E-MEH-PRT-DR-16.07.2009.xls

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

I attached it via step three. Not sure how else to do this.

Any suggestions? I could email it direct if you want.

Unfortunately the security firewall will not let me have access to the Mediafire site as noted below against step three

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

ii36259
to be
at
or not to be
bellhelicopter
that
dot
is
textron
the
dot
question
com

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry that should be ii36250

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

rofl

Gerry

 

[SkipVought]

TB or not TB:
That is conjestion.
Consumption be done about it?
Of cough, of cough,
But it will take a lung, lung time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Sent forms this morning. But I just received a sys-admin msg. saying that the gateway had a connection timeout after four hours, it will keep trying for upto five days.

Hopefully it will turn up soon

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

My suggestion would be to dump the Text Boxes and rather use the large merge area for your users to enter their free text. Be abolutely certain to UNCHECK the LOCKED property of these merge cell areas.

Here is some code that will loop thru the sheet grabbing the user-entered data...

Sub test()
    Dim r As Range, rMerge As Range, sPrev As String
    
    For Each r In ActiveSheet.UsedRange
        With r
            If Not .Locked Then
                Set rMerge = .MergeArea
                If rMerge.Address <> sPrev And .Text <> "" Then
                    Debug.Print .Text, rMerge.Address
                End If
                sPrev = rMerge.Address
            End If
        End With
    Next
End Sub

Here are the Debug.Print results, after removing ONE TEXTBOX in row 36, and notating as described above...
[tt]
E-BAM-PRT $C$6:$F$6
01.03.2009 $H$6:$J$6
DADILOVEANU Daniel $M$6:$Y$6
DADILOVEANU Daniel $D$12:$H$12
31.01.2009 $I$12:$K$12
OTHER $P$12:$Y$12
During the yesterday I tried to send these new Thales accounts on the Thales portal to CCK and I managed to open the portal. There is another opportunity to send that trouble ticket to cck? $C$34:$Y$53
Y $H$61
N $M$61
Y $H$62
N $M$62
Y $H$63
N $M$63
HQ decide when we move PG2 $P$64:$Y$64
Y $H$65
N $M$65
Y $H$68
N $M$68
Y $H$69
N $M$69
Y $H$70
N $M$70
Y $H$71
N $M$71
Y $H$72
N $M$72
Y $H$73
N $M$73
Y $H$74
N $M$74
Y $H$75
N $M$75
Y $H$76
N $M$76
Y $H$77
N $M$77
Y $H$78
N $M$78
Y $H$79
N $M$79
6 $G$118
0 $H$118
3 $I$118
2 $G$119
0 $H$119
1 $I$119
2 $G$120
0 $H$120
1 $I$120
8 $I$121
1 $H$122
3 $I$124
a $H$142
27.02.2009 $J$142:$M$142
a $H$143
27.02.2009 $J$143:$M$143
a $H$144
27.02.2009 $J$144:$M$144
a $H$145
27.02.2009 $J$145:$M$145
a $H$146
27.02.2009 $J$146:$M$146
a $H$147
27.02.2009 $J$147:$M$147
a $H$148
27.02.2009 $J$148:$M$148
a $H$149
27.02.2009 $J$149:$M$149
a $H$177
a $K$177
27.02.2009 $Q$177:$T$177
a $H$178
a $K$178
27.02.2009 $Q$178:$T$178
a $H$179
a $K$179
27.02.2009 $Q$179:$T$179
a $H$180
a $K$180
27.02.2009 $Q$180:$T$180
a $H$181
a $K$181
27.02.2009 $Q$181:$T$181
a $H$182
a $K$182
27.02.2009 $Q$182:$T$182
a $H$183
a $K$183
27.02.2009 $Q$183:$T$183
a $H$184
a $K$184
27.02.2009 $Q$184:$T$184
a $H$187
a $K$187
27.02.2009 $Q$187:$T$187
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Thanks Skip.

That would be how I originally started out as you can see from the now locked merged cells. I changed to the text boxes because of the missing data issue I mentioned earlier.

However, I still have two years worth of reports to go through, that have text boxes, so my original problem still exists, regarding empty textboxes.

I will have to issue a new template at a suitable time, but that will not be immediately.

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

dim shp as shape
for each shp in activesheet.shapes
  msgbox shp.oleformat.object.caption
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}