Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-28-2020, 02:10 PM
marceepoo marceepoo is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 7 64bit Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2010 64bit
Novice
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a
 
Join Date: Sep 2012
Posts: 22
marceepoo is on a distinguished road
Default Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a

I made a userform named, “frmTOE_ExhMakeFromData01” which contains a textbox control named, “txHyperlinkzName”

The textbox control disappeared. I assumed that I must have inadvertently deleted it during the weeks I’ve been working on the userform, and I tried to recreate the textbox, “txHyperlinkzName”

But when I tried to assign the named “txHyperlinkzName” to the new textbox control, a message from Windows VBA said:


Could not set the Name property. Ambiguous name.

Hoping to make the textbox control prominent in the userform so that I could find it among the many controls on the userform, I ran a sub which assigned a vibrant color to the control’s background property, and I assigned text to the textbox’s text property. To no avail; the textbox still was not visible.

I ran the following sub to get a list of all of the controls in the userform:

Code:
Sub subUserformControls_Output2TxFile(sFormName As UserForm)
    'https://stackoverflow.com/questions/25248034/how-do-i-list-print-control-names-and-or-properties-on-a-vba-form
    Dim ctlLoop As MSForms.Control
    Dim sMsgboxMsg As String
    Dim sDateTime As String
    sDateTime = CStr(Format(Now(), "yyyy-mm-dd_hh-mm-ss"))
    sMsgboxMsg = sDateTime
    
    For Each ctlLoop In sFormName.Controls
        sMsgboxMsg = sMsgboxMsg & vbCrLf & _
           CStr(TypeName(ctlLoop)) & ":" & CStr(ctlLoop.Name)
    Next ctlLoop
    
    Call subWrite2TxtFileAndOpenInEditPadPro(sMsgboxMsg)
    
End Sub ' subUserformControls_Output2TxFile(sFormName)
I think that the foregoing is the sub I used (and of which I was very proud); but the sub now only produces the error message:
Run-time error '91':
Object variable or With block variable not set

That sub or some other version (of that sub) which I inadvertently deleted, produced an output listing all of the controls in the form, and the list did indeed include a textbox control named, “txHyperlinkzName”

Questions:
A. What did I do to cripple the sub above?
B. What do I do now?
1. Should I just create a new textbox control and put it where the missing one was, and continue working on the form? or ...
2. Should I presume that the file is corrupt and that continuing to work on its development is a waste of time? In other words, should I start over on my userform from scratch? or ...
3. I created my userform in the VBA IDE, dragging textboxes, labels, cmd buttons, etc. onto a canvas. But I think people with more knowledge than I have, create userforms entirely by VBA. In other words, are there VBA techniques I could use to:
a. Extract from my userform all the properties of each control (frames, textboxes, labels, cmd btns, option btns, etc.) including the location information (i.e., where they were situated in the userform, and the size (length and width) of the control)?
b. Use the extracted info to re-create the userform?

Thank you for any advice.

Marc
Reply With Quote
  #2  
Old 04-28-2020, 06:33 PM
Guessed's Avatar
Guessed Guessed is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 10 Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

We can't see which line is failing so we can't tell you where the issue is but the error is saying the object doesn't exist.

To find your lost item, put your report into a MsgBox instead of using a text file that you need to go and read using more macros.

Add a command button to your form and then press F7 to create the macro for it. Paste the following code into that macro
Code:
  Dim ctlLoop As control, sMsg As String
  sMsg = "Name, Top, Left" & vbCr
  For Each ctlLoop In Me.Controls
    sMsg = sMsg & ctlLoop.Name & ", " & ctlLoop.Top & ", " & ctlLoop.Left & vbCr
  Next ctlLoop
  MsgBox sMsg
Then display the form and click that button. It will display a list of the controls on the userform and their top and left position values. This should include your missing item and what its coordinates are.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 04-28-2020, 06:51 PM
Guessed's Avatar
Guessed Guessed is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 10 Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

And to answer your later questions, no I don't create userforms with code. I use the GUI to create and layout the controls and then heavily customise macros on a lot of those controls to get the functionality I want. I guess I could create and populate the controls via code but that is additional work that isn't required.

I have used code to populate content in labels and controls but only very rarely would I actually create the controls themselves with code. The only time I would do that is when the actual number of controls needed isn't known at design time (ie the code reads data from somewhere that specifies how many controls are needed).
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 04-28-2020, 09:05 PM
gmayor's Avatar
gmayor gmayor is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 10 Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

It sounds as though the errant text box is either off the form area where it is supposed to be or hidden behind another element. Select the element where the text box was placed e.g. Userform, Frame etc and check its properties in the Properties dropdown. All the items that element contains should be listed. Select the 'missing' item in that list and you should be able to locate its position in the editor bring it back into view.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 04-30-2020, 06:46 PM
marceepoo marceepoo is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 7 64bit Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2010 64bit
Novice
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a
 
Join Date: Sep 2012
Posts: 22
marceepoo is on a distinguished road
Default

I followed the very helpful suggestion you posted at 04-28-2020 06:33 PM:

Quote:
Originally Posted by Guessed View Post
We can't see which line is failing so we can't tell you where the issue is but the error is saying the object doesn't exist.

To find your lost item, put your report into a MsgBox instead of using a text file that you need to go and read using more macros.

Add a command button to your form and then press F7 to create the macro for it. Paste the following code into that macro
Code:
  Dim ctlLoop As control, sMsg As String
  sMsg = "Name, Top, Left" & vbCr
  For Each ctlLoop In Me.Controls
    sMsg = sMsg & ctlLoop.Name & ", " & ctlLoop.Top & ", " & ctlLoop.Left & vbCr
  Next ctlLoop
  MsgBox sMsg
Then display the form and click that button. It will display a list of the controls on the userform and their top and left position values. This should include your missing item and what its coordinates are.
I had to send the output (sMsg) to either the clipboard or a file because sMsg had 72 lines. I saved the clip to a csv file and then to the attached Excel file.

The coordinates of "txHyperlinkzName" are: .Top=162 and .Left=504

"txHyperlinkzName" is visible when I run the following:

Code:
Private Sub btnMoveTxtbox_Click()
    Me.txHyperlinkzName.Top = 134
    Me.txHyperlinkzName.Left = 24
    Me.txHyperlinkzName.BackColor = &HC000&
End Sub
But "txHyperlinkzName" disappears when I close the form, presumably returning to those weird coordinates: .Top=162 and .Left=504
I spent almost the entire day today trying unsuccessfully to figure out how to get the userform, “frmTOE_ExhMakeFromData01”, to save those coordinates into the form, so that "txHyperlinkzName" is situated at those coordinates when I am editing the form.

I await your next suggestion.
And I would like to thank you again.

Marc
Attached Files
File Type: xlsx userform_controls_locations.xlsx (11.7 KB, 5 views)
Reply With Quote
  #6  
Old 04-30-2020, 07:14 PM
marceepoo marceepoo is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 7 64bit Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2010 64bit
Novice
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a
 
Join Date: Sep 2012
Posts: 22
marceepoo is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
It sounds as though the errant text box is either off the form area where it is supposed to be or hidden behind another element. Select the element where the text box was placed e.g. Userform, Frame etc and check its properties in the Properties dropdown. All the items that element contains should be listed. Select the 'missing' item in that list and you should be able to locate its position in the editor bring it back into view.
Graham, you are absolutely right, as shown in my posting Today, April 30, 2020, at 06:46 PM, i.e., the errant text box is off the form area where it is supposed to be.

Until about 5 min ago, I didn't understand your suggestion:
Quote:
Select the element where the text box was placed e.g. Userform, Frame etc and check its properties in the Properties dropdown. All the items that element contains should be listed.
But after I started doing what you told me to do, it became obvious and wonderful. I saved the new location coordinates in the VBA IDE, and now I can resume working on the userform.

If it won't take more than a few moments of your time or Guessed's time, I would like to know how to use VBA to save those values to the userform. If it takes more than a few moments, don't bother. You both have been very helpful, and I don't want to take advantage of you.

Marc

Thanks again, to both of you.
Reply With Quote
  #7  
Old 04-30-2020, 07:41 PM
Guessed's Avatar
Guessed Guessed is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 10 Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Marc

I'm not sure what you mean by 'save those values to the userform'. Just by moving the control on the userform when editing the layout you are effectively saving those values.

You can use the GUI to reposition any control on the userform (assuming you can select it). You can also use a macro such as the following to position the form itself or move a control to a particular position on the form. Because of its name, this macro will autorun when the form is loading so the form will open showing your control in its rightful position.

Code:
Private Sub UserForm_Initialize()
  With Me
    .StartUpPosition = 0
    .Left = Application.Left + 0.5 * (Application.Width - .Width)
    .Top = Application.Top + 0.5 * (Application.Height - .Height)
    .txHyperlinkzName.Top = 134
    .txHyperlinkzName.Left = 24
    .txHyperlinkzName.BackColor = &HC000&
  End With
End Sub
I use this code generically on all of my userforms (apart from your control named lines) to always position my userform in the middle of the application window. This saves lots of angst when using multiple screens.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 05-01-2020, 11:06 AM
marceepoo marceepoo is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 7 64bit Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2010 64bit
Novice
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a
 
Join Date: Sep 2012
Posts: 22
marceepoo is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
Marc

I'm not sure what you mean by 'save those values to the userform'. Just by moving the control on the userform when editing the layout you are effectively saving those values.

You can use the GUI to reposition any control on the userform (assuming you can select it). You can also use a macro such as the following to position the form itself or move a control to a particular position on the form. Because of its name, this macro will autorun when the form is loading so the form will open showing your control in its rightful position.

Code:
Private Sub UserForm_Initialize()
  With Me
    .StartUpPosition = 0
    .Left = Application.Left + 0.5 * (Application.Width - .Width)
    .Top = Application.Top + 0.5 * (Application.Height - .Height)
    .txHyperlinkzName.Top = 134
    .txHyperlinkzName.Left = 24
    .txHyperlinkzName.BackColor = &HC000&
  End With
End Sub
I use this code generically on all of my userforms (apart from your control named lines) to always position my userform in the middle of the application window. This saves lots of angst when using multiple screens.
To answer your question, Andrew, even though the encircled VBA code (on the right side of the image below) did set "txHypterlinkzName" in the location encircled in blue below on the userform, "txHypterlinkzName" would not be in that location later when I was editing the userform (i.e., dragging controls from one place to another).
In other words, after I ran the Initialize sub and subsequently closed the userform (after showing it), "txHypterlinkzName" would return to a location off the screen, which made it hard to continue editing the userform.

Graham's instructions enabled me to get to txHypterlinkzName's Properties Window (encircled in green in the attached image file, to the left).
Once I got access to txHypterlinkzName's Properties Window, I was able to manually insert the settings that are encircled in red, below to the left, inside the green box.

After I inserted those settings, I executed a Windows/Word [File, Save] on the form.
Now, when I am editing the userform, "txHypterlinkzName" continues to be situated at the location encircled in blue below.

It would be nice to know how to use vba to perform the equivalent of me manually inserting the settings in the properties window, and thereafter clicking, File, Save.

You showed me how to get and set the controls' coordinates programmatically, which was very helpful to me.

But assume that I have a large userform with many controls that need to be renamed (instead of having their coordinates changed) programmatically (which is a problem I remember having more than once), it would be nice to know how to save the changes to the form, so that the changes are embedded in the form when I resume editing the form.

Much thanks, again,

Marc
Reply With Quote
  #9  
Old 05-01-2020, 09:56 PM
Guessed's Avatar
Guessed Guessed is offline Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Windows 10 Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Marc

I haven't seen controls randomly relocating themselves so I'm not sure why you are having these issues. The position you put the object should be the position it shows up as when the form is displayed UNLESS you have code also running that either hides or moves it. Looking at your code, there is some confusion as to whether you are aiming at 114/504 or 134/24 but lets assume you have checked through all your code and verified that no code is actually moving the control.

One left field possibility (since you have obviously been working on this issue for a while) is that you have made two copies of the userform and are inadvertently editing one but displaying the other.

It is a bad idea to programmatically rename controls. Change the value of the displayed label or content in the control by all means but don't go changing the name of the control.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Find and replace query re missing spaces Johanna Word 3 11-22-2019 01:06 AM
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Macro to Find and Replace Does Not Work - But Works Manually Rod_Bowyer Excel Programming 7 10-14-2018 11:49 PM
Userform textbox control missing/invisible; can't find, use or replace it; how to salvage work on a Find and Replace doesn't work. Bop70 Word 3 02-04-2015 11:45 AM
Userform is invisible in Project box for old templates billy8b8 Word VBA 4 07-19-2014 03:44 PM
Find and replace No longer work TJH Word 3 03-25-2014 11:33 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft