Posts by btadams

Re: Docking A Custom Toolbar

Take a look at the Position property in Help:

For Each bar In CommandBars
    If bar.Visible = True Then
        If bar.BuiltIn Then
            bar.Position = msoBarTop
         Else
            bar.Position = msoBarBottom
        End If
    End If
Next

Re: Display Data In A Geographical Map

Have a look here.

http://www.xcelsius.com/Examples/Overview.html

I haven't tried it but would be interested to hear from others if it's worthwhile.

Re: Macro Module Only Returns 15 Of 20 Digits

try replacing this:

Range("K2").Value = WedgeData(1)

with this:

Range("K2").Value = Cstr(WedgeData(1))

Re: Offset Function With Validation

try a dynamic named range like this:

=OFFSET(Sheet2!$A$1,0,0,COUNT(Sheet2!$A$1:$A$100), 1)

Re: Changing The Cell Color With A Certain Value Is Entered

Select the cells you want this to happen in, then go to Format -> Conditional Format. You will need to apply two conditional formats (click the Add button after entering the first CF)

Re: Uable To Share Macro As Add-in Or Template

Can you post the code?

Re: Hide a row before printing based on a cell value

Maybe this will do (but it doesn't do anything if B30 =1):

Private Sub CommandButton1_Click()
  
    If ActiveSheet.Name = "Sheet1" Then
    Cancel = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Range("B30").Value > 1 Then
      With ActiveSheet
          .Rows("31:37").EntireRow.Hidden = True
          .PrintOut
          .Rows("31:37").EntireRow.Hidden = False
      End With
    ElseIf Range("B30").Value < 1 Then
      With ActiveSheet
          .Rows("31:37").EntireRow.Hidden = False
          .PrintOut
      End With
    End If
    ActiveSheet.Rows("31:37").EntireRow.Hidden = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End If
 End Sub

Re: Override Alert Message Text

As far as I know there is no way to override that message. You can either password protect the sheet to keep users from unprotecting it, or you might turn off protection altogether and use a macro such as this in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End Sub

Re: Combine/merge 51 Workbooks In Same Folderr

Take a look at this thread:

http://www.ozgrid.com/forum/showthread.php?t=67113

Re: Vba To Insert Command Button

Here's some code generated with the macro recorder with some minor changes:

Sub AddButton()
Dim strBname As String
'
    ActiveSheet.Buttons.Add(Range("B5").Top, Range("B5").Left, 89.25, 23.25).Select
    strBname = Selection.Name
    Selection.OnAction = "Personal.xls!AddLimitsToChart"
    Selection.Characters.Text = "Add Limits"
    With Selection.Characters(Start:=1, Length:=10).Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 10
    End With
    Range("H12").Select
End Sub

Re: Two Worksheet_selectionchange

Unless I'm missing something (and everyone says I am), it looks like you could combine all of that code within the same SelectionChange event. Just remove the extra

If Target.Cells.Count > 1 Then Exit Sub

Re: Select A Range With VBA

Or this:

Range(Cells(15, 1), Cells(15, 1).End(xlDown)).Select
Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select

Re: Two Worksheet_selectionchange

Why would you need two of them?

Re: Filling A Table With Unique Random Number

Andy Pope has his version here:

http://www.andypope.info/fun/sudoku.htm

in the file that has first/last names in separate columns you could combine them by inserting a new column and entering the formula =A2&" "&B2 where A2 has first name and B2 has last name. Then before deleting the columns A & B, you'll need to select all of the column containing the formula (click on the column header letter), click your copy button and then go to Edit -> Paste Special and click the Values button and OK. This will change the formulas to values and you can then delete columns A & B

Then insert or delete columns where you need to in order that both files have equal number of columns in same order.

Then select all the data in one worksheet (use the Ctrl + Shift +Arrow keys simultaneously to select in a certain direction) and copy/paste it at the bottom of the other worksheet. Then you can select all the columns with data and go to the Data menu and choose Sort. Sort by the column with names. Then you can get rid of duplicates by going to Data -> Filter -> Advanced Filter. Select your entire database and choose to paste it to another location and check off the Unique Records Only checkbox

try this:

=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$1:$1))

=INDEX(Sheet2!$B$1:$B$5,MATCH(A1,Sheet2!$A$1:$A$5,0))

change the absolute references to the ranges in your worksheet

you could put some code in the Workbook_Open event:

Private Sub Workbook_Open()
Range("A1").value = Range("A1").value + 1
End Sub

To enter this, right-mouse click on the Excel icon next to the File menu and choose View Code

try this:

Sub DleteDups()
Dim Cell As Range

Do While ActiveCell.Offset(1, 0) <> ""
If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If
Loop
End Sub

Lynda,

there is a function that comes with the Analysis Toolpak add-in that does exactly what you want called WORKDAY. To load the add-in go to the Tools menu and select Add-Ins... and check off the Analysis Toolpak and Analysis Toolpak VBA boxes. Then in the Date & Time category you will find WORKDAY. It automatically skips weekends and optionally any holidays you supply.