Search:

Type: Posts; User: Jaafar Tribak; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.31 seconds.

  1. Re: VBA change sheet on data validation selection

    Untested :


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Set rng = Range("B4")
    If Target.Address = rng.Address Then
    For Each ws...
  2. Re: Get address of cell with conditional formatting in VBA

    Assuming the interior highlight color is red, you could use the DisplayFormat Property... Something along these lines:


    If Range("A53").DisplayFormat.Interior.Color = vbRed Then

    MsgBox...
  3. Re: [VBA] manually-select ActiveX commandbuttons & change font size

    Not necessarly true... You CAN actually run code and change the commandbutton attributes even in design mode .
  4. Re: Macro stops at this line: Public Declare Function apiShowWindow Lib "user32"

    Public Declare PtrSafe Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
  5. Re: Creating Userform Controls At Runtime

    If you just need to add two commandbuttons then yes just a Private WithEvents declaration line for each control created and adapt the Add_Control Event Handler as you did but you need to set the...
  6. Re: Creating Userform Controls At Runtime

    How about not using the Visual Basic Extensibility library and just sinking the newly created commandbutton at runtime ?

    Are you happy with this :

    In the UserForm Module:

    Option Explicit
    ...
  7. Re: Creating Userform Controls At Runtime

    Can you show us a sample of the code that doesn't work.
  8. Re: Custom Dynamic Tool Tips Excel Userform

    Or you could keep the entire code inside the userform module which I think is a better approach... Something along these lines:

    In the UserForm Module:

    Option Explicit

    Private WithEvents ws...
  9. Re: Custom Dynamic Tool Tips Excel Userform

    This assumes that the Userform is UserForm1, the commandbutton is CommandButton1 and the Target cell is Cell Sheet1!A1 ... Change the latters as required.


    Place the code in the worksheet module...
  10. Re: Highlight active cell or selection but restore previous fill color

    I use excel 2016 and it is necessary to force a calculation otherwise the screen doesn't refresh.

    If I use Application.ScreenUpdating=True then it works and avoids having to calculate the...
  11. Re: Extracting modules from a different workbook via VBA? (corrupt file)

    Have you tried something like this assuming the corrupt excel file and the expoting workbook are both opened in the same excel instance:


    Call...
  12. Replies
    2
    Views
    75

    Re: Macro button on desktop

    As suggested by Kamolga, you could have a file shortcut on the desktop that opens the workbooks.

    Maybe have a vbscript shortcut clicking on which will prompt the user with the MsgBox .
  13. Re: Highlight active cell or selection but restore previous fill color

    Hers is the logic behind the code:

    First, the code checks if the worksheet is un-protected. If so it protects the interface only so that the user cannot edit the sheet but the vba code can.
    ...
  14. Re: ActiveWindow.Zoom - Need to change value but not sure how

    One thing that might work is by sinking the application events from the personal workbook which should override the existing selection_change code in the other workbooks .


    Put the following code...
  15. Re: Highlight active cell or selection but restore previous fill color

    Remove the existing worksheet protection and paste the code below in the worksheet module:
    (Change password in red as required.)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ...
  16. Re: Highlight active cell or selection but restore previous fill color

    You could unprotect the worksheet at the start of the code and protect it back at the end or you could protect the interface only
  17. Re: ActiveWindow.Zoom - Need to change value but not sure how

    If you don't need code on those workbooks, can you not just open them with macros disabled?
  18. Re: Highlight active cell or selection but restore previous fill color

    Forcing a calculation upon each selection has a bad impact on performance specially if the workbook contains lots of formulaes.

    You can try this alternative :


    Private Sub...
  19. Replies
    15
    Views
    141

    Re: Closing & Opening Files

    Sorry - the variable should be declared as String :

    Try this :

    Sub Copy()
    Dim strFilename As String
    Dim strActiveWbName As String

    strActiveWbName = ActiveWorkbook.Name
    ...
  20. Replies
    15
    Views
    141

    Re: Closing & Opening Files

    True. I missed the fact that the code was closing the active workbook before opening the new copy-workbook.

    See if this works for you:


    Sub Copy()
    Dim strFilename As String
    Dim...
  21. Replies
    15
    Views
    141

    Re: Closing & Opening Files

    What is the output in the immediate window of the following line:

    Debug.Print Dir(FilePathName)
  22. Replies
    15
    Views
    141

    Re: Closing & Opening Files

    What if you introduce a small delay as follows :

    Sub Copy()
    Dim strFilename As String
    strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " &...
  23. Re: Flickering spreadsheet due to VBA codes

    This worked for me :

    In a Standard Module:

    Option Explicit

    #If VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal...
  24. Re: Flickering spreadsheet due to VBA codes

    Try using the SetTimer API to avoid the screen flickering.
  25. Replies
    8
    Views
    283

    Re: VBA - Identifying Image Selected

    Try this :

    Option Explicit

    Private WithEvents cmbrs As CommandBars


    Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
    End Sub
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4