Where are the object sizing commands?

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Where are the object sizing commands?

Post by bknight »

I just tried to do a small bit of object copy/paste/size/location. I haven't done this since the 2000 version. I know the ribbon replaced the drawing tool, but like where is the pointer arrow that allows objects to be selected? Mine are buttons that have macros assigned and the normal selection process does not allow me to select object(s)

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

A button that has a macro assigned to it is a Form control. You can select it by right-clicking on it.
Select Size and Properties from the context menu to format the button.

If you want to select multiple buttons:
On the Home tab of the ribbon, in the Editing group, click Find & Select > Select Objects.
You can now select a group of buttons (and other objects) by dragging a rectangle around them. Alternatively, use click and Ctrl+click.
Don't forget to turn off Select Objects when you're done.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

Something is wrong I right click on the object, and nothing appears.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

Additionally, when using the select tools I am unable to select objects, the arrow draws a blue rectangle where I "selected", but no objects selected.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

That makes me think that the buttons are not Form controls but ActiveX controls (and your claim that they have macros assigned to them was misleading).
On the Developer tab of the ribbon, click to turn on Design Mode. See if you can then select the buttons by left-clicking them.
Turn off Design Mode afterwards.

If you don't see the Developer tab of the ribbon, you can make it visible in File > Options > Customize Ribbon.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

OK now we're churning a bit, I could not select multiple items, but holding down the Ctrl buttons I was able to select multiple objects.
I'm not taking offense, however there are codes assigned to the two buttons.

One aspect that you will probably understand. I added a "button" because of my inability to select previous buttons and this object won't group with the two previous ones. So, I may delete the new object and start over.
All this work deals directly from my attempts to code and delete files in the UAC restricted folder.
Sorry for all my bother. :scratch:

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

Here is an image of a new sheet with a command button's property page. The first image is a right click on the object. Now then how do I attach a macro?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

While Design Mode is on, double-click the button.
This will activate the worksheet module in the Visual Basic Editor and create (or activate) its On Click event procedure.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

Ok, did that but I get a message something to the effect only comments may appear after end sub

Code: Select all

Private Sub CommandButton1_Click()
Run Test
End Sub


Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOWNORMAL = 1

Sub Test()
    ShellExecute 0, "runas", "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE", Command, vbNullString, SW_SHOWNORMAL
    Dim xl As Excel.Application
    Set xl = GetObject(, "Excel.Application")
    With xl
        .Visible = False
        .Workbooks.Open ThisWorkbook.FullName
        .Run "DeleteOldFiles"
    End With
End Sub


Sub DeleteOldFiles() ' Delete scan results older than two months
    ' Path of top folder
    Const strFolder = "C:\Program Files (x86)\Tradestation 9.5\Scans"
    Dim objFSO As Object
    Dim objFolder As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFolder)
    'Set objShell = CreateObject("Shell.Application")
    'Set FSO = CreateObject("Scripting.FileSystemObject")
    ''strPath = FSO.GetParentFolderName(WScript.ScriptFullName)
    'If FSO.FileExists(strPath & "\MAIN.VBS") Then
     'objShell.ShellExecute "wscript.exe",        Chr (34) & strPath & "\MAIN.VBS" & Chr(34), "", "runas", 1
     'Else
     'MsgBox "Script file MAIN.VBS not found"
     'End If
    Call DeleteFiles(objFolder)
End Sub

Sub DeleteFiles(objFolder As Object)
    Dim objFile As Object
    Dim objSubfolder As Object
    ' Check files
    For Each objFile In objFolder.Files
        If LCase(objFile.Name) Like "*.tsrslts" Then
            If objFile.DateLastModified < Date - 60 Then
                'objFile.Delete.Permission = True
                objFile.Delete
            End If
        End If
    Next objFile
    ' Check subfolders
    For Each objSubfolder In objFolder.SubFolders
        Call DeleteFiles(objSubfolder)
    Next objSubfolder
End Sub

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

All the code that starts with Private Declare should not be in the worksheet module, but in a standard module (the kind you create by selecting Insert > Module)
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

GRRRRRR

Ok, did that and clicked on the button. It starts a new instance of Excel in "Admin" mode. The bad aspect, the Excel part of the taskbar disappears, the VBA editor is still present. When I started an Excel attempted to open a workbook I received an error message that the workbook was open already. But as I indicated the Excel was missing in the task bar.

I restarted the pc to get back to normal. Can you explain where the missing taskbar icon was?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

Your Test macro sets the Visible property of the Excel application to False. So Excel is still running but you don't see it anymore.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

:cheers: Set it to true, and it is still there as you indicated.

However, the delete code didn't run, no error message and one of the folders still contains a Sep file.
Stepping through the code it encounters the line
.Run "DeleteOldFiles" at that the Excel "flickers" and yes it appears that the same workbook is open in a new? instance but the "DeleteOldFiles" doesn't run.
Looks like another failure attempt, unless you may think up a solution.
Thanks for the time an effort.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

I don't understand why you're using ShellExecute...
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

It was in one of the many places I looked, just copied the code. Does it have to do with opening Excel in Administrator mode? That was the intent.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

I'm not an expert on such stuff, but I fear it won't help...
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

After working a lot over the weekend, I fixed the issues of the Code, added two command buttons and assigned code to each. The only way I was successful was to run the code in two pieces the first was to run Excel as Administrator then when that was done run the delete files code.
Now I'll forget this the next time I run the two codes, so I added another form control label. After typing in the instructions and resizing the label I wanted to increase the text font.
Where is that control?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where are the object sizing commands?

Post by HansV »

You can't change the font size of a Form Control label directly. But you can do the following:
- Enter the text for the caption of the label in a cell.
- Change the font size of the cell to the size that you want.
- Select the label.
- Click in the formula bar and point to the cell with the text.
- Press Enter.
The label will use the font size of (the first character of) the cell.
In the screenshot below, I used a cell on the same sheet, but you can use a cell on another sheet instead.

S0988.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Where are the object sizing commands?

Post by bknight »

Thanks, It is strange that you can't set the font directly.