Posts by max_lux

Hi, anyone know why the VBA Project password inputbox would pop up when (right after) I close down a workbook? It is specific to one workbook, and I am just wondering why it might do this...

Thanks,

Re: Enable Right Click In Textbox

Hi Andy,

1) I didn't realize that I had the sheets/ textboxes backwards. It was just an example. My actual sheets are named differently.
2) Yes I know ctrl+c and ctrl+v works, but I'm amazed I am still teaching guys about that. I work with some dinosaurs that barely know how to shut a pc down, nevermind understand what a shortcut key is. If they don't have a mouse to point and click, they are :censored:ed

Thanks for the code Andy you rock

Re: Enable Right Click In Textbox?

I would the text in textbox on sheet1 copied to a textbox on sheet2 at the end of the last text using either copy and paste, which the users are used to doing, or they could paste the whole text and edit it later in sheet2 textbox.

Having them manually select and paste selected text would be best, but I could live with giving them the other option of pasting the text with a commandbutton.

I have included a workbook to possibly better describe what I mean.

Oh, and thanks for the help m8, it is appreciated greatly. I've only been here for a short time on this forum, but it is my new favorite place in the world lol!, and it's because of guys like you, Norie, Dave, gmccreedy, and the rest. Hats off to you all.
:ole:

Hi,

I use textboxes on my workbook sheets for certain things. My users like the way they function more like a word document than a regular excel cell. One failing I have found is that the user cannot select cut copy or paste when using these textboxes on a worksheet. Is there any way to enable or emulate this function?
Use of commandbuttons would be acceptable.
Thanks, in advance,

Re: Remove Objects

Norie,

hehe i suppose it could work, but I have so much code already written and in place to do what I want already, I don't think I can justify a rewrite to do that.

Andy, Thanks man, that's what I was looking for. I already moved extesive code from more than a dozen sheets to a module, but that will clean up the scraps and make the emailed sheet lean and mean :thanx:

Re: Remove Objects

Just tryed that edit>goto....
That might be the key fo rme thanks,

data wouldn't work. The sheets are set up as reports.

Re: Remove Objects

Oh, to clarify, I am trying to figure this code out, because that way I don't have to figure out the object number of each individual object or group for everything on every sheet is wish to delete the objects from, and then assign delete for the specific object.

Re: Highlight First Cell in Column of Selected Cell Row

ah roger - then that wont work for me in this case. I just want to disable the specific sub when running another sub, which the code I posted works great for. Thanks for the help Andy

Re: Remove Objects

I could do this all day, but unfortunately, it's a waste of my time. I shall now leave it in the hands of you gurus

Private Sub CommandButton2_Click()
Dim obj As CommandButton
next_obj:
    If obj.Count > 0 Then
        Application.obj(obj.Count).Value
        obj.Remove obj
    ElseIf obj.Count > 0 Then GoTo next_obj
    Else: obj.Count = 0
    End If
End Sub

Re: Remove Objects

Getting closer...?

Private m_objs As Object

Private Sub CommandButton2_Click()
next_obj:
If m_objs.Count > 0 Then
Application.m_objs(m_objs.Count).Value
m_objs.Remove m_objs
ElseIf m_objs.Count > 0 Then GoTo next_obj
Else: m_objs.Count = 0
End If
End Sub

Re: Remove Objects

or maybe something like?

Private m_objs As Collection


Private Sub CommandButton2_Click()
next_obj:
    If m_objs.Count > 0 Then
        Application.m_objs(m_objs.Count).Value
        m_objs.Remove m_objs.Count
    If m_objs.Count > 0 Then GoTo next_obj
    Else: m_objs.Count = 0
End Sub

Re: Remove Objects

or maybe something like?

Private m_objs As Collection 


Private Sub CommandButton2_Click() 
     ' remove controls
    Do While m_objs.Count > 0 
        Me.Controls.Remove m_objs(m_objs.Count).Name 
        m_objs.Remove m_objs.Count 
    Loop

Upon copying a sheet to a new book, I would like to remove the objects (commandbuttons) from the sheet prior to saving it.

I am not sure of what code I could use to accompllish this, but it might look something like?

Sub del_objs()
    Dim obj As Integer
        For obj = 1 To Objects.Count
        Objects(obj).Delete
    Next obj
End Sub

Re: Highlight First Cell in Column of Selected Cell Row

I guess I meant - will it only disable events for a the private sub if it is in a private sub? or if this is strictly an application event handler?

Re: Remove Code From Copied Sheet?

I work oilfield. I can track up to 6 drilling rigs events per day. For each rig I make a daily report. For each of these rigs I generate two weekly reports.
To make these sheets smaller in size and to protect password info is why I would like to do this. I usually leave a commandbutton on each report for the main office to be able to print with if viewing the report(s), but I could remove that easy enough.

I use the following code to generate one of these weekly reports:

Private Sub CommandButton3_Click()
    Application.ScreenUpdating = False
    With Assistant
    .Animation = msoAnimationSendingMail
    End With
    ActiveWorkbook.Unprotect Password:="______"
    Sheets("I").Select
    Range("d13").Select
    Sheets("I").Copy
    ActiveSheet.Unprotect Password:="______"
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
    
    Dim StrName As String
    On Error GoTo Last
    StrName = ActiveWorkbook.Sheets("I").Range("AE10").Value
    ChDir "C:\"
    StrName = Application.GetSaveAsFilename(StrName, _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If StrName = "False" Then GoTo Last
    ActiveWorkbook.SaveAs StrName
    
Last:
    Application.DisplayAlerts = False
    ActiveWindow.Close


    Sheets("I").Select
    Range("d13").Select
    ActiveSheet.Protect Password:="______", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveWorkbook.Protect Password:="______"
    Application.ScreenUpdating = True
End Sub

If this is a big problem, I can move the code to a module. Protecting the VB project is not an option. My workbook is distributed and used by others.

Re: Highlight First Cell in Column of Selected Cell Row

Will this only disable events for this sub?

I was just looking at a sheet of mine, and realized that I had a password on it in the private code. Since I copy this report page to a new book and then send it in to an office, is there any way of either:

1) stripping the code from a sheet copied to new book sheet?, or
2) protecting the VBA project on the sheet copied to new book?

Thanks,

Re: Highlight First Cell in Column of Selected Cell Row

I ended up having errors with either your or my edit, so I combined them and it works 100% no errors now.

Private m_rngPrevTarget As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub


If ActiveWorkbook.Sheets("sheet1").Range("AA1").Value = 1 Then GoTo skipPrevTgt


    If Not Intersect(Target, Columns("D:W")) Is Nothing Then
        If Not m_rngPrevTarget Is Nothing Then
            With m_rngPrevTarget
                .Offset(0, -.Column + 1).Interior.ColorIndex = xlNone
            End With
        End If
        Set m_rngPrevTarget = Target
        With m_rngPrevTarget
            .Offset(0, -.Column + 1).Interior.ColorIndex = 6
        End With
    End If
skipPrevTgt:
     
End Sub

When running code on the page, I set the cell AA1 to 1, run the code, and then set it to "". This allows the code to run without removing color formatting.

Thanks so much for your help Andy

Re: Highlight First Cell in Column of Selected Cell Row

I figured out how to work around this. If I have my macro change a cell number to 1 and then add an if then statement, I can bypass using the code until i change the cell number back to 1 at the end of the macro.

Private m_rngPrevTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If ActiveWorkbook.Sheets("sheet1").Range("A1").Value = 1 Then GoTo skipPrevTgt


    If Not Intersect(Target, Columns("D:W")) Is Nothing Then
        If Not m_rngPrevTarget Is Nothing Then
            With m_rngPrevTarget
                .Offset(0, -.Column + 1).Interior.ColorIndex = xlNone
            End With
        End If
        Set m_rngPrevTarget = Target
        With m_rngPrevTarget
            .Offset(0, -.Column + 1).Interior.ColorIndex = 6
        End With
    End If
skipPrevTgt:
     
End Sub

Re: Highlight First Cell in Column of Selected Cell Row

Thanks for your help and response. I get an error 424 when I select a cell...

Private m_rngPrevTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    If Not Intersect(Target, Columns("D:W")) Is Nothing Then
        If Not m_rngPrevTarget Is Nothing Then   ' <<< error this line
            With m_rngPrevTarget
                .Offset(0, -.Column + 1).Interior.ColorIndex = xlNone
            End With
        End If
        Set m_rngPrevTarget = Target
        With m_rngPrevTarget
            .Offset(0, -.Column + 1).Interior.ColorIndex = 6
        End With
    End If
     
End Sub

Cancel last - I got an error until I moved other code in the worksheet to below this code. Now it works perfectly. Thank-you so much!

I can't thank all you great guys in this forum enough. I have learned how to do things on Ozgrid via free code and forum that I have wanted to do for five years. This place :rock: !!!

Ah I have found one problem - Because I use more rows than I showed in my sheet example, I use macros to hide or show more or less columns. When I ran a macro, because more than one cell was selected, it left the whole area...

here's a screen to show - better than 1000 words...