Cannot clear Alternative Text from Checkbox using VBA (in Access)

Michael Paravicini
Michael Paravicini used Ask the Experts™
on
I create an excel table with checkbox with no caption using vba. This works fine but if the same table is create a second time, the checkbox has alternative names which I cannot delete. This is the code I use:

Function AddCheckBox(objExcel As Object, lTab, lRow, lCol, lCaption As Variant)
Dim lColChar, lName As Variant

lColChar = AssignChar(lCol)
objExcel.Worksheets(lTab).CheckBoxes.Add(Left:=Range(lColChar & lRow).Left, Top:=Range(lColChar & lRow).Top, Width:=Range(lColChar & lRow).Width, Height:=Range(lColChar & lRow).Height).Select
With Selection
  .Caption = lCaption
End With
End Function

I have also tried the following but in this case I get an error on the second time I try to create the excel table (first time works fine).

Function AddCheckBox(objExcel As Object, lTab, lRow, lCol, lCaption As Variant)
Dim lColChar, lName As Variant
Dim s As Shape

'On Error Resume Next
lColChar = AssignChar(lCol)
lName = RandomString(10)
objExcel.Worksheets(lTab).CheckBoxes.Add(Left:=Range(lColChar & lRow).Left, Top:=Range(lColChar & lRow).Top, Width:=Range(lColChar & lRow).Width, Height:=Range(lColChar & lRow).Height).Select
With Selection
  .Caption = lCaption
  .Name = lName
  objExcel.Worksheets(lTab).Shapes(lName).AlternativeText = ""
End With
End Function

Thank you so much for any help. Regards Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Can you post a complete sample of your code...also on clarification side ,  you are using Access to create the Excel ?

Author

Commented:
Hi John, yes I create the table from Access. The funny thing is that the first time it always works fine. The second time it wil either crash with the message "Method range of object - 'Global' failed" or it will print "Checkbox #" as the alternative text (which it never does first time thru).

The call is embbedded as follows:

Do While lOffer = rst!OfferCat
       
          ' Print Category (Vorspeise, Hauptspeise)
          lCat = rst!RecipeCat
         
          Do While lCat = rst!RecipeCat Or lSortByOffer = False
            ' Print Getränke
            Call AddSymbol(objExcel, lTab, lRow + 1, 1, "     ", 6, 1, 118, " " & DLookup("Name" & lLang, "Getränke", "GetränkeID=" & rst!GroupRezeptId), "Wingdings")
            objExcel.Worksheets(lTab).Cells(lRow + 1, 3).Value = DLookup("VP", "Getränke", "GetränkeID=" & rst!GroupRezeptId)
            Call AddCheckBox(objExcel, lTab, lRow + 1, 5, "")
            lRow = lRow + 1
            rst.MoveNext
            If rst.EOF Then
              Exit Do
            End If
          Loop
          If rst.EOF Then
            Exit Do
          End If
        Loop
        lRow = lRow + 1
      Loop
John TsioumprisSoftware & Systems Engineer

Commented:
Take a look at my code
I am not sure what you are after
Public Function DeleteCheckBox()
Dim xl As Object
Dim wb As Object
Dim sh As Object
Set xl = CreateObject("Excel.Application")
Set wb = xl.workbooks.Open("PATH TO YOUR EXCEL")
Set sh = wb.sheets(1)
With sh
For Each chk In .CheckBoxes
chk.Caption = chk.Caption & "Something"
Next
wb.Save
For Each chk In .CheckBoxes
Debug.Print chk.Caption
If InStr(chk.Caption, "2") Then
chk.Delete
End If
Next
wb.Save
wb.Close

Set xl = Nothing
End With

End Function

Open in new window

At first i iterate the checkboxes of the Excel (if any)...and i add some text to its caption....
I save the worksheet...
Then again i iterate the checkboxes and i delete the checkboxes that have the number "2" in their caption...
You will have to make some adjustments to your case...

Keep in mind that if you have added a checkbox with specific name then you can't add it again (its already added)...you just change it or Delete it....
Also from the watch window it seems that CheckBoxes don't carry the AlternativeText property....
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior Developer
Commented:
First of all: declarations in VBA work different than VB. When a type is not specified, then it is always Variant. E.g.

Dim a, b As String 

Open in new window

is in equivalent to

Dim a As Variant
Dim b As String

Open in new window


Depending on what you do, you should use concrete data types instead of Variant. This helps to avoid runtime errors or weird behavior due to implicit casts, which makes debugging simpler.

When working with controls, then you need to change your coding style from "general macros" to a more developer centric view. This means here working with your controls:

Option Explicit

Public Sub Test()

  Dim NewSheet As Excel.Worksheet
  
  Set NewSheet = ActiveWorkbook.Sheets.Add
  NewSheet.Select
  NewSheet.Name = RandomString("Test")
  AddCheckBox NewSheet, 3, 3, "Caption1"
  AddCheckBox NewSheet, 4, 3, "Caption2"
  AddCheckBox NewSheet, 5, 3, "Caption3"
  AddCheckBox NewSheet, 6, 3, "Caption4"
  AddCheckBox NewSheet, 7, 3, "Caption1"
  Set NewSheet = Nothing
  
End Sub

Public Function RandomString(APrefix As String)
  
  Randomize Timer
  RandomString = APrefix & CLng(Rnd() * 1000)
  
End Function

Public Function AddCheckBox(AWorksheet As Excel.Worksheet, ARowNumber As Long, AColumnNumber As Long, ACaption As String)
 
  Dim CheckBox As Excel.CheckBox
  Dim DestinationRange As Excel.Range
  
  Set DestinationRange = AWorksheet.Cells(ARowNumber, AColumnNumber)
  Set CheckBox = AWorksheet.CheckBoxes.Add(DestinationRange.Left, DestinationRange.Top, DestinationRange.Width, DestinationRange.Height)
  CheckBox.Caption = ACaption
  CheckBox.Name = RandomString("Dummy") 
  Set CheckBox = Nothing
  Set DestinationRange = Nothing
 
End Function

Open in new window


Capture.PNGThe alternative text is the same as the Caption.

So I don't really see you problem, maybe you need to rephrase it.

btw, what has a table do with that problem?
EE29157517.xlsm

Author

Commented:
Thank you John - just a quick question. Where is the definition of chk (as in For Each chk In .CheckBoxes)? I get an error on chk "variable not defined"?? Thanks Michael
John TsioumprisSoftware & Systems Engineer

Commented:
Probably you have Option Explicit
Just put a
Dim chk As Variant

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Thanks to @Ste5an contribution ... i found the AlternativeText ...is "hidden"
chk.ShapeRange.AlternativeText

Open in new window

So you can alter it as caption
chk.ShapeRange.AlternativeText = "Whatever you Want"

Open in new window


I am afraid i don't do much work with Excel extra Controls...  :)
ste5anSenior Developer

Commented:
@john:
Yup, that's correct.

@Michael:
But still: What is the problem?
The alternative text property should have a menaingful content, cause it is used be the accessibility tools in Windows. Thus being equal to the caption is not a problem.
John TsioumprisSoftware & Systems Engineer

Commented:
I guess he uses it for some other use....probably he is pushing extra info

Author

Commented:
Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial