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'

 

[Walter349]

Skip,

Thanks an interesting routine, but it does not really do what I was looking for.

I have come up with this, which does what I want textbox contents wise, but I cannot get it to focus on the required cell or column. it keeps overwriting the previous cell.

the rng1 value does not seem to be picking up the required cell in the detination worksheet (Sh). it dumps the textbox contents into the preceding cell and column, overwriting what was entered there.

This Rng1 statement works ok outside of the 'with' statement

I tried using SET, but that caused an 'Object required'error

with Sh1.shapes("text Box 3").textframe
rng1 = Sh.Cells(Rows.Count, 4).End(xlUp) (2).Characters.text

For lngIndex = 1 to .chracters.count step 255
	strTemp = strTemp & .characters(lngIndex, 255).text
next

rng1 = strTemp
rng1.wraptext = true
end with

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

 

[Walter349]

Horray,

Resolved it,

The 'set' statement must go outside of the 'with' loop.

The report now populates the way I wanted it to and no more out of sync cells due to blank text boxes

Thanks for all the help

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

 

[SkipVought]

it keeps overwriting the previous cell.

I do not see where you are assigning the value to ANY cell.

Skip,

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

 

[SkipVought]

This may work better for you

    Dim shp As Shape
    
    For Each shp In ActiveSheet.Shapes
        With shp
            If .Type = msoTextBox Then
                Debug.Print .OLEFormat.Object.Caption
            End If
        End With
    Next

BUT fundamental problem with textbox as a data entry receptacle is mapping the data along with the other data that is entered in cells.

Skip,

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

 

[Walter349]

Skip,

I found something very similar to that last night, but could not see how it was getting into the destination worksheet cells.

I tried to find information on the OLEformat and its use, to get a better understanding and really did not find anything that was very clear at all.

Tomorrow I will post the part of the code that now works for the text boxes. I cannot post the whole thing as I would have to type it out from scratch, (different network)

But what I have got, does work, ran it through 150 of the forms and no problems, other than where the users had changed the textbox names for some reason, that was annoying!

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

 

[SkipVought]

other than where the users had changed the textbox names for some reason, that was annoying!

I thought that you had the sheet protected???

Skip,

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

 

[Walter349]

Sheet yes, but did not lock the textboxes, frankly it did not occur to me that they would do such a thing. What would be the point?

Well live and learn

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

 

[SkipVought]

So do you have everything nailed down?

Skip,

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

 

[Walter349]

At the moment I cannot identify a way to lock down the text box, so that the label cannot be changed.

but the next version of the form will return to the merged cells, so its not an issue at the moment.

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